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
Expand all | Collapse all

SYMPTOMS

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'.
Back to the top | Give Feedback

CAUSE

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.
Back to the top | Give Feedback

WORKAROUND

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.
Back to the top | Give Feedback

MORE INFORMATION

The following code demonstrates this issue: 
   create database dynamicSQL
   go
   use dynamicSQL
   create table employee(Name varchar(255), salary money)
   go
   create proc TestError @MySql nvarchar(500) As 
   exec (@mySql)
   go
   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: 
   go
   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: 
   go
   setuser 
   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.
Back to the top | Give Feedback

Properties

Article ID: 301299 - Last Review: October 16, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbprb KB301299
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top