asked
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
eqt.id =
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.
"Are both databases owned by the same login? " Yes, but the application users that are having this issue are not the owner (windows authenticated user). They are SQL authenticated users that the application uses to talk to the database. Thanks Brian. All our objects are owned by dbo, makes things easier. I will look into the cross database chaining. Is this something new in SQL Server 2012? In the past, I haven't had this kind of problem with with querying tables/views between different dbs in a stored proc. THanks.
dbo maps to the database owner. Query sys.databases to see who the owner is for each of the databases. Databases don't necessarily have the same owner. If they don't, the ownership chain would fail, even if the DBs are configured for cross database ownership chaining.
20 People are following this question.
