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?

1 Answer 1

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.

4
  • 3
    Another alternative is signing the stored procedure with a certificate based on a user with the needed database access and permissions.
    – Dan Guzman
    Mar 1, 2022 at 12:36
  • J.D. thanks for the information, i am experimenting a bit with both the option. At the moment i get few problem with both the way, but i would try to solve them by myself. If i will get nowhere, how can i ask you for some hint? May i open a new specific question or add a comment here? --EIDTED i have to discard first option because i need to support statement TRUNCATE TABLE in a chained procedure. AFAIK (from documentation i read in the last few hours), the TRUNATE TABLE operation is not supported.. so maybe this is the cause of my issue with first option
    – Skary
    Mar 1, 2022 at 15:40
  • 1
    @Skary, the certificate signing option I mentioned in my comment solves both the cross-database chaining issue and the elevated permissions for TRUNCATE. Here's one example answer.
    – Dan Guzman
    Mar 1, 2022 at 17:30
  • @Skary Feel free to comment on here for any clarification. I'll take a look into options for using TRUNCATE when I get a free moment. My second option should allow you to use that command. Also please take a look into Dan's idea as well. That may actually be the best solution so far, IMO.
    – J.D.
    Mar 1, 2022 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.