Microsoft KB Archive/301299

= PRB: Security Context of Dynamic SQL Statements Inside a Stored Procedure =

Article ID: 301299

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q301299



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 denied on object ' ', database ' ', owner ' '.



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.



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.



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.

Additional query words: permissions security context grant revoke deny exec adhoc

Keywords: kbprb KB301299

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.