locked
Create a user that can access two views that uses another database and see there result RRS feed

  • General discussion

  • Hi

    I managed to create a user to access only two view resides on MY_DATA (If i login with sql management studio using User1 i can see the two view), the problem is I cannot query the views because of 
    "User1" is not able to access the database "MY_YEAR" under the current security context

    The view in MY_DATA uses table in MY_YEAR

    I used this script

    USE MY_DATA;
    GO
    create LOGIN User1 with password = 'User1'
    GO
    CREATE USER User1 FROM LOGIN User1;
    GO
    GRANT SELECT ON MY_DATA.dbo.VIEW1 TO User1;
    GRANT SELECT ON MY_DATA.dbo.VIEW2 TO User1;
    
    What security i should add to give it the right to query the view?


    .Net Blog VFP Blog

    Wednesday, February 7, 2018 3:16 PM

All replies

  • USE MY_YEAR;
    GO
    CREATE USER User1 FROM LOGIN User1;
    GO

    GRANT SELECT on table_you_useinview  TO User1 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Wednesday, February 7, 2018 3:21 PM
  • GRANT SELECT ontable_you_useinview  TO User1

    You mean I should grant select to each table used by the view in database MY_YEAR?

    And if the user login he will see the tables in database MY_YEAR by Sql Management studio?

    What if I don't want to show these tables? 

    Cannot I just give access to execute the view with whatever tables the view is using?

    Thank you in advance


    .Net Blog VFP Blog

    Wednesday, February 7, 2018 5:56 PM
  • ...

    You mean I should grant select to each table used by the view in database MY_YEAR?

    And if the user login he will see the tables in database MY_YEAR by Sql Management studio?

    What if I don't want to show these tables? 

    Cannot I just give access to execute the view with whatever tables the view is using?

    ...

    This is exactly the point:

    1. You COULD use cross-database-ownership-chaining (enabled per database), but this imposes a security risk as it could potentially open other access path as well that you may not have in sight right now.
    2. or you go with this option by Uri, to have the user in both databases
    3. or if those tables include other columns that should not be seen, you could create the same views (preferably inside a dedicated schema) in the target database (My_year) and point to those from the source-database (my_data). Then you also need the user in both databases but you get away with granting select only to those views
    4. you could also use stored procedures instead and use execution context switch inside (can even be a certificate-based user)

    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Saturday, February 10, 2018 1:20 PM