Dynamic SQL and Ownership Chaining in SQL Server

By: K. Brian Kelley | Comments (1) | Related: > Security

Problem
I have tried to use ownership chaining like in a previous tip, but it doesn't seem to be working. In my case, I'm building a SQL string (dynamic SQL) to execute within the stored procedure and then executing using either EXEC or EXEC sp_executesql. However, I keep getting an access denied error when the string executes. Both objects are located in the same schema and neither object as an explicit owner defined. What am I doing wrong?

Solution
Whenever you use EXEC or EXEC sp_executesql to execute a string containing SQL, SQL Server actually starts the execution in a separate batch. If we look at the Using sp_executesql topic in Books Online, we see the following explanation:

When either sp_executesql or the EXECUTE statement executes a string, the string is executed as its own self-contained batch. SQL Server compiles the Transact-SQL statement or statements in the string into an execution plan that is separate from the execution plan of the batch that contained the sp_executesql or the EXECUTE statement. The following rules apply for self-contained batches:

The EXECUTE topic documentation in Books Online has further guidance:

Permissions are not required to run the EXECUTE statement. However, permissions are required on the securables that are referenced within the EXECUTE string. For example, if the string contains an INSERT statement, the caller of the EXECUTE statement must have INSERT permission on the target table. Permissions are checked at the time EXECUTE statement is encountered, even if the EXECUTE statement is included within a module.

To sum this up, when we use dynamic SQL within another module, such as a stored procedure, the dynamic SQL executes in a separate batch. As a result, the ownership chain is broken. Actually, all batch related functionality is interrupted. For instance, consider the following bit of code. We'll set a particular set of batch settings (ARITHABORT and ANSI_WARNINGS) outside of the dynamic SQL, change them within the dynamic SQL, and then test them again once the dynamic SQL has completed. This is taken from the Batch Execution Environment and MARS topic in Books Online:

PRINT 'Outside Dynamic SQL Execution:'; 
SET ARITHABORT ON; 
SET ANSI_WARNINGS ON; 
PRINT 1/0; 
PRINT '---------------------------------'; 

PRINT 'Inside Dynamic SQL Execution:'; 
DECLARE @SQL NVARCHAR(MAX); 
SET @SQL = 'SET ARITHABORT OFF; 
            SET ANSI_WARNINGS OFF; 
            PRINT 1/0; 
            PRINT ''---------------------------------'';' 
EXECUTE sp_executesql @SQL; 

PRINT 'Back outside Dynamic SQL Execution:'; 
PRINT 1/0;

If we execute this set of queries, we'll see the following result:

Dynamic SQL as a separate batch

The changes we made to ARITHABORT and ANSI_WARNINGS only persisted within the execution of the dynamic SQL. Once that was completed, the settings return to what they were prior to executing the dynamic SQL. This is even though we made no settings changes after the dynamic SQL completed. This shows us that the dynamic SQL is indeed being executed as a separate batch. And because it is a separate batch, we must have permission to perform the query on the referred to object. Let's set up an example:

USE MSSQLTips; 
GO 

CREATE USER LimitedUser WITHOUT LOGIN; 
GO 

CREATE ROLE ExampleRole; 
GO 

EXEC sys.sp_addrolemember 'ExampleRole', 'LimitedUser'; 
GO 

CREATE TABLE dbo.ATable (TableInt INT); 
GO  

CREATE PROCEDURE dbo.AProc 
AS 
BEGIN 
   DECLARE @SQL NVARCHAR(MAX); 
   SET @SQL = 'SELECT TableInt FROM dbo.ATable'; 
   EXECUTE sp_executesql @SQL; 
END; 
GO 

GRANT EXECUTE ON dbo.AProc TO ExampleRole; 
GO

We can test the execute as the LimitedUser:

EXECUTE AS USER = 'LimitedUser'; 
GO 

EXEC dbo.AProc; 
GO 

REVERT; 
GO

When we do, we'll get an error for the result:

SELECT denied on dynamic SQL

There are two options. The first is to simply grant the appropriate permission on the referred to object, such as:

GRANT SELECT ON dbo.ATable TO ExampleRole; 
GO

And if you go back and execute as LimitedUser again, there is no error. The catch here is that the user will now be able to access the object directly. For instance, we've granted SELECT permissions against the dbo.ATable table. And in our example that's not a big deal. But if you're talking INSERT, UPDATE, or DELETE rights against a large table, that may be a big deal.

If you're still using SQL Server 7.0 or 2000, you're stuck. There's no other option here. However, if you're using SQL Server 2005 or 2008, you can use the EXECUTE AS clause as part of the stored procedure declaration. For instance:

REVOKE SELECT ON dbo.ATable TO ExampleRole; 
GO 

DROP PROCEDURE dbo.AProc; 
GO 

CREATE PROCEDURE dbo.AProc 
WITH EXECUTE AS OWNER 
AS 
BEGIN 
   DECLARE @SQL NVARCHAR(MAX); 
   SET @SQL = 'SELECT TableInt FROM dbo.ATable'; 
   EXECUTE sp_executesql @SQL; 
END; 
GO 

GRANT EXECUTE ON dbo.AProc TO ExampleRole; 
GO

I included the REVOKE SELECT and DROP PROCEDURE to perform clean-up, so that we can be sure the EXECUTE AS clause within the CREATE PROCEDURE statement is working (and also so we don't get an error executing the CREATE PROCEDURE statement). But if you go back and execute as LimitedUser, like when the SELECT permissions was explicitly granted, the call to execute the stored procedure works successfully.

Next Steps

To learn more about object chaining refer to these tips:

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Dynamic SQL and Ownership Chaining in SQL Server

About the author

Comments For This Article