Cross database user permissions

  • I have two databases: Db1 and Db2. For security (and other) reasons I don't allow the database users direct access to the tables or views so all SELECTs, INSERTs, etc are inside stored procedures. All works fine except when a stored procedure in Db1 needs to access data in DB2. I have to grant the user SELECT (or other) permission to the table  in Db2 that is being accessed by the stored procedure from Db1 - which basically defeats the whole "do not allow direct access" stuff.

    Is there a way to create roles, schemas, anything to allow a stored procedure from one database full access to another database in such a way that the user does not get the direct access to the tables?

    Thanks

    • This topic was modified 1 week, 4 days ago by  JacekO.

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • Yes, it is possible. There are three different ways to go:

    1. Enable cross-database ownership chaining.
    2. Certificate signing.
    3. EXECUTE AS.

    While the first is the simplest, it also opens for a security risk, as its permits users in the database who permissions to create users and procedures to elevate to get sysadmin rights.

    As for the other two. I am not going to explain them here, but refer you to my article Packaging Permissions in Stored Procedures where I describe all this detail. Unfortunately, it is a quite a long article, and the chapter on cross-database access is at the end. But once you have gotten through it, you should be able to make an informed decision.

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

  • Did not have time to fully digest the entire article, but the pieces I managed to read were great. Thanks a lot. Will have to experiment a bit since the procedures in Db1 are in a different schema (Web) than the tables in Db1 and Db2 (both dbo).

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • This was removed by the editor as SPAM

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

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