CODEWITHSUNDEEP
sql-serverdatabaseaccess-rights
Richard B
Richard B

Reputation: 935

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

Upvotes: 0

Views: 151

Answers (1)

sepupic
sepupic

Reputation: 8687

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.

Upvotes: 2

Related Questions