Today I had a co-worker come to my desk wondering why his service was getting a permissions error when selecting from a table. His confusion was rooted in the fact that his service has been using this stored procedure for over a year. He is granting access to the underlying tables by granting execute rights to the stored procedure only.
This method works most of the time but ownership chaining is not fool proof. The reason that he was getting select permissions errors was because he had changed his stored procedure to include some dynamic SQL. He was using sp_executesql but even if he was using EXECUTE instead he would have ran into the same problem. The sp_executesql article on MSDN states:
When either sp_executesql or the EXECUTE statement executes a string, the string is executed as its own self-contained batch. SQL Server compiles the Transact-SQL statement or statements in the string into an execution plan that is separate from the execution plan of the batch that contained the sp_executesql or the EXECUTE statement.
This is where ownership chaining breaks down. An ownership chain is completely within the context of a batch. Since we now know that sp_executesql and EXECUTE create new batches when they compile, we end up stepping out of the security context that we want to be in. If you want to grant execute rights only, you are going to have to avoid dynamic SQL, unfortunately.
Leave a Reply