Cross database user permissions

• JacekO

  SSCertifiable

  Points: 6610

  More actions

  July 17, 2021 at 4:06 am

  #3911111

  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 3 years, 10 months ago by JacekO.

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

• Erland Sommarskog

  SSC-Insane

  Points: 24038

  More actions

  July 17, 2021 at 7:43 am

  #3911134

  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]

• JacekO

  SSCertifiable

  Points: 6610

  More actions

  July 18, 2021 at 4:04 am

  #3911321

  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]