Query data from another database

  • c.myny

    Grasshopper

    Points: 11

    Hi,

    I have several databases but I only want to give a user the rights to do something on one database.
    In the database the user has rights he can select from a view, a stored procedure etc...
    But in that view or stored procedure there is a query which gets data from another database where the user does not have rights.

    Situation
    Database A
    User1 has rights to do anything on this database

    ALTER view Mydata
    SELECT * FROM DatabaseB.dbo.MyTable
    GO

    If the user User1 doe do a Select * FROM DatabaseA.dbo.MyData  he/she should get the data from DatabaseB but as User2

    Database B
    User1 does not have rights to do something
    User 2 has all the rights
    MyTable

    In fact any user from database A should be translated to  User2 when getting data from database B  
    How must I do this in SQL 2008

  • Erland Sommarskog

    SSC-Insane

    Points: 23828

    This is possible, but it takes a bit of reading and understanding to get there. I have an article on my web site which discusses the option in details, but i will take you some time to get to the end: Packaging Permissions in Stored Procedures.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • c.myny

    Grasshopper

    Points: 11

    Erland Sommarskog - Monday, March 11, 2019 6:27 AM

    This is possible, but it takes a bit of reading and understanding to get there. I have an article on my web site which discusses the option in details, but i will take you some time to get to the end: Packaging Permissions in Stored Procedures.

    Also views are involved, is that also described is you article.

  • Erland Sommarskog

    SSC-Insane

    Points: 23828

    If the views goes goes cross-database, that is you have views in database A that accesses tables in database B, and you want users to be able to access the views directly, without calling stored procedures or multi-statement functions, the users will need to have access to at least the database. If you have the access to the views in stored procedures, this is no different from access to tables.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • c.myny

    Grasshopper

    Points: 11

    Erland Sommarskog - Monday, March 11, 2019 6:42 AM

    If the views goes goes cross-database, that is you have views in database A that accesses tables in database B, and you want users to be able to access the views directly, without calling stored procedures or multi-statement functions, the users will need to have access to at least the database. If you have the access to the views in stored procedures, this is no different from access to tables.

    I understand but isn't there a possibility to do a sort of mapping of all users of database A to another user who has the rights an database B when executing stored procedures, or perform selects from a view or function.
    So that every query going from database A to database B is always as a specific user. As there is the EXCUTE AS but that does not work for views.

  • Erland Sommarskog

    SSC-Insane

    Points: 23828

    I'm at a client and with limited time that I can help users in forum. And I did write this article so that I don't have to say the same thing over and over again. So I kindly refer you to my article.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply