0

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

Richard B
  • 895
  • 13
  • 39

1 Answers1

2

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.

sepupic
  • 8,409
  • 1
  • 9
  • 20
  • Thanks for your reply. I'm far from an expert on SQL Server so can I just clarify a couple of things you are saying? the tables in both databases are all X.dbo and Y.dbo. My understanding of the significance of the dbo part has always eluded me. Does that satisfy your requirements? – Richard B Nov 20 '17 at 10:29
  • If your proc and both your tables are in dbo schema (A.dbo.my_sp, B.dbo.Y) and no one manually changed authorization to smth else, then all your objects do have the same owner (dbo). You should also check if database owners are the same and enable DB_CHAINING on both databases – sepupic Nov 20 '17 at 10:35
  • OK. Thanks. I think I have it now – Richard B Nov 20 '17 at 11:12