I need a user with dbo permissions in one db (db1) and read only permissions in a second db (db2) to execute a stored procedure in db1 that delete records in db2.
So, usr1 is sql user with dbo permissions in db1 and read only in db2
there is a usp in db1 that delete rows in db2
Also, usr2 has dbo permissions in both db1 and db2
I tried impersonation:
grant execute db1.usr1.usp to usr1 as usr2
and
grant execute on object::db1.usr1.usp to usr1 as usr2
but I'm getting error:
Msg 15151, Level 16, State 1, Line 33
Cannot find the object 'usp', because it does not exist or you do not have permission.
What am I doing wrong?
(I need usr1 to remain read only in db2 and delete records only via db1.usr1.usp)
As Cross-DB Ownership Chaining is not enabled by default (and should not be enabled due to security concerns), Usr1 should have the exec permission to the sp in db1 and delete permissions on the tables with rows to be deleted in db2. A better way to do is not to have Usr1 in db2 and sign the sp in db1 with the certificate user in db2 having the delete permissions.
hi Ifor
after seeing your post I read about module signing... but as we change several modules in a daily basis and we've 30+ installations with different versions, re-signing would be of order.
anyway, I did it the old way and now is working... thank you very much for you help.