5

I have to premit that i am not a DBA but i have the need to mantain a SQL Server instance and have few problem with stored procedure permission chaining (hope terms is correct).

To brifiley explain my needs, i have an user that should have grant only on one DB that should run a stored procedure (on that "granted" DB) that contain reference to an external DB (i should inherit somewhat grant)

In details, my scenario is that i have three DBs

  1. Configuration DB (should be read by everyone but never written by no-one except Configuration User)
  2. Staging DB (should be read/written by staging procedure)
  3. Final DB (should be read/written by app)

So i have three users

  1. Configuration User : used to mantain data in Configuration DB
  2. Staging User : used by many staging procedure (very long procedures, may take hours) that place data into the Staging DB
  3. Final User : used by the application that read the data when staging procedure finished to deploy data in Staging DB and put it back Final DB. Final User can access every DB (at least for reading)

So far so good, staging procedure do their work and when complete write a semaphore (to notify data are ready). Sometimes the application (that use Final User) check the semaphore and ingest all the data into the Final DB.

Now i have to support a fast, on-demand staging procedure (i still need to execute in from Staging DB for other reasons) that when completed, should update directly the data into Final DB.

I have writte such procedure and everything work as intended, but i have to grant read/write operation on FinalDB to Staging User and i don't like it. There are many application that use Staging User and some of them is not "under my control", so i don't wont that a faulty application that should write data into Staging DB will end up messing with Final DB. I can accept that my new on-demand staging procedure may be critical (and end up messing with Final DB).

So the question is, Staging User is already granted to execute a stored procedure (EG : OnDemandUpdate) because it is part of Staging DB but that contain insert/update over an external table (from Final DB) and i want that the grant can be chained. How can i do that?

Skary
  • 368
  • 2
  • 11

1 Answers1

4

I think what you're looking for is cross-database ownership chaining:

Cross database ownership chaining is an extension of ownership chaining, except it does cross the database boundary.

Please see Microsoft's Books Online for more information. It is possible to turn it on only for individual databases which may make more sense for your use case:

You can set cross-database ownership chaining for individual databases using the SET clause of the ALTER DATABASE statement. If you are creating a new database, you can set the cross-database ownership chaining option for the new database using the CREATE DATABASE statement.

Alternatively, another solution could be to use the EXECUTE AS statement to impersonate a Login who has access to the table in the Final database.

You would put it inside the procedure that the Staging User has access to execute, and then only that procedure would have access to modify the table in the Final database.

With this solution the Staging User would need access to be able to impersonate the Login being used though. This is better than directly granting Final access to the Staging User because you are controlling your database through stored procedures, and ideally the Login it will be impersonating only has limited access (just the single table you need to update?) in the Final database.

J.D.
  • 40,776
  • 12
  • 62
  • 141