question

sherridn avatar image
sherridn 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?
sql-server-2012stored-procedurespermissions
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Actually I made a mistake in the above query. It should be: SELECT DISTINCT pn.field1, eqt.field2, eqt.field3 FROM table2 eqt LEFT OUTER JOIN db1.dbo.table1 pn on eqt.id = pn.id
0 Likes 0 ·
Are both databases owned by the same login?
0 Likes 0 ·

1 Answer

· Write an Answer
K. Brian Kelley avatar image
K. Brian Kelley answered
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.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

"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.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.