Search code examples
sql-serverdatabaseaccess-rights

Cross Database Grants in SQL Server

I have a stored procedure X in Database A.

X, among other things, updates table Y in Database B

I have granted execute on X to User1 but when User1 calls X it gets the following error:

The UPDATE permission was denied on the object 'Y', database 'B', schema 'dbo'.

How do I correctly grant the update rights to User1? I do not want to directly grant Insert/Update/Delete rights on table Y to User1

Solution

  • If all your objects have the same owner (dbo) and database owners are the same all you need is to enable cross-database ownership chain, i.e. you should do for both databases 

    ALTER DATABASE A SET DB_CHAINING ON;  
ALTER DATABASE B SET DB_CHAINING ON;

    Link: Enabling Cross-Database Access in SQL Server.

    User1 should be mapped to the second database.