Dynamic SQL and Ownership Chaining in SQL Server

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 ONSET ANSI_WARNINGS ONPRINT 1/0PRINT ‘———————————‘
PRINT ‘Inside Dynamic SQL Execution:’DECLARE @SQL NVARCHAR(MAXSET @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:

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 MSSQLTipsGO 

CREATE USER LimitedUser WITHOUT LOGINGO 

CREATE ROLE ExampleRoleGO 

EXEC sys.sp_addrolemember ‘ExampleRole’, ‘LimitedUser’GO 

CREATE TABLE dbo.ATable (TableInt INTGO  

CREATE PROCEDURE dbo.AProc 
AS 
BEGIN 
   DECLARE @SQL NVARCHAR(MAX   SET @SQL = ‘SELECT TableInt FROM dbo.ATable’   EXECUTE sp_executesql @SQLENDGO 

GRANT EXECUTE ON dbo.AProc TO ExampleRoleGO

We can test the execute as the LimitedUser:

EXECUTE AS USER = ‘LimitedUser’GO 

EXEC dbo.AProcGO 

REVERTGO

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

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 ExampleRoleGO

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 ExampleRoleGO 

DROP PROCEDURE dbo.AProcGO 

CREATE PROCEDURE dbo.AProc 
WITH EXECUTE AS OWNER 
AS 
BEGIN 
   DECLARE @SQL NVARCHAR(MAX   SET @SQL = ‘SELECT TableInt FROM dbo.ATable’   EXECUTE sp_executesql @SQLENDGO 

GRANT EXECUTE ON dbo.AProc TO ExampleRoleGO

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:

Resources

Community

Subscribe