Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
PRB: Security Context of Dynamic SQL Statements Inside a Stored Procedure
Article ID: 301299 - View products that this article applies to.
This article was previously published under Q301299
When you are running a stored procedure with a dynamic execution query (sp_executesql or EXECUTE), you may receive the following error message:
Server: Msg 229, Level 14, State 5, Line 1 'permission' permission denied on object 'object', database 'database', owner 'owner'.
This behavior occurs because a dynamic execution query (sp_executesql or EXECUTE) executes in a separate context from the main stored procedure; it executes in the security context of the user that executes the stored procedure and not in the security context of the owner of the stored procedure.
Note: You should take this behavior into account while you are determining ownership chains.
To work around this issue:
The following code demonstrates this issue:
The following code reproduces this problem:
Use the following code to drop the test dynamicSql database and logins used in this reproduction scenario:
For more information about ownership chains, see Using Ownership Chains in SQL Server Books Online.