Microsoft small business knowledge base

Article ID: 301299 - Last Review: October 16, 2003 - Revision: 3.2

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:
  • 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
   use dynamicSQL
   create table employee(Name varchar(255), salary money)
   create proc TestError @MySql nvarchar(500) As 
   exec (@mySql)
   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

   setuser '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:
   use master
   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.

  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
kbprb KB301299
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support