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:
- You have to correctly grant the necessary permission for each underlying object that is mentioned in the dynamic execution query.
- You can execute a SELECT statement with the INTO clause to create a temporary table that contains all the data in the original table, and then EXEC your SQL statement against the temporary table. This is a viable solution if the tables that you are dealing with are small.
The following code demonstrates this issue:
create database dynamicSQL
create table employee(Name varchar(255), salary money)
create proc TestError @MySql nvarchar(500) As
set nocount on
insert employee select 'FunctionFunction', 100000
insert employee select 'Function', 30000
set nocount off
exec sp_addlogin 'FunctionFunction'
exec sp_adduser 'FunctionFunction'
exec sp_addlogin 'Function'
exec sp_adduser 'Function'
grant execute on TestError to Function
The following code reproduces this problem:
declare @Sql varchar(500)
set @Sql = 'select * from employee where Name = ''FunctionFunction'''
exec TestError @Sql
Use the following code to drop the test dynamicSql database and logins used in this reproduction scenario:
drop database dynamicSql
exec sp_droplogin 'FunctionFunction'
exec sp_droplogin 'Function'
For more information about ownership chains, see Using Ownership Chains
in SQL Server Books Online.