Issue with cross database query in stored proc permissions
I am using SQL Server 2012. We have 2 databases db1 and db2. We have 2 users, user1 and user2, that the application uses, that have limited permissions on both database. I wrote a stored proc that is in db2, but, in one of the queries, references a table in db1. The stored proc has "Grant Execute on stored_proc1 to user1, user2". When running this stored proc, the application gives "Select Permissions was denied on object db1.dbo.table1". Here is basically the query: SELECT DISTINCT pn.field1, eqt.field2, eqt.field3 FROM table1 eqt LEFT OUTER JOIN db1.dbo.table2 pn on
pn.id This is not a dynamic SQL query, so I am not sure why it would need SELECT permissions on the table in db1. I don't remember having this issue in other versions of SQL Server. Also, besides Granting Select on the table, is there another way to resolve this issue?
There is, but it requires turning on cross database ownership chaining on both databases. Also, it would require that the stored procedure's owner be the same as the table's owner. Objects like stored procedures and tables don't have explicit owners unless they are manually set. So in the standard case, they use the owner of the schema they are in. If you're within the same database, that stays at the user level. However, when it comes to cross database ownership chaining (if turned on), SQL Server will carry the match all the way up to the login. So if both objects are in the dbo schema for their respective databases, the owner of the databases would have to be the same. Obviously, enabling cross database ownership chaining should be considered carefully. An ownership chain could be formed between objects you didn't intend.