Using Dynamic SQL inside a Stored Procedure

Preventing direct access to database tables for users is a widely considered “best practice” for many DBAs. The pattern typically looks like User -> Stored Procedure -> Tables. Since the stored procedure owner has access to the referenced tables, the user can be granted EXECUTE access to the stored procedure without requiring them to have SELECT access to the tables. However, if you use dynamic T-SQL inside a stored procedure, that security model breaks. T-SQL executed via EXEC ('some T-SQL') or EXEC sys.sp_executesql @cmd will execute under the security context of the caller, not the principal who created the stored procedure. Luckily, SQL Server offers a simple workaround for this problem, defining the stored procedure using the WITH EXECUTE AS OWNER clause.

The Execution of Marie Stuart - Abel de Pujol (after using Dynamic T-SQL without parameters)

The Execution of Marie Stuart (after using Dynamic T-SQL without parameters) – Abel de Pujol

The Setup

Before we get into the WITH EXECUTE AS OWNER clause, lets look at the problem with some code. I find code, with output, is often easier to understand than a description of the problem.

Lets create a database we can use for testing:

Next, we’ll create a table, and add some data to it.

Next we’ll create a stored procedure that returns results from the table.

We need a login and user to complete the setup in a manner similar to what you’re likely to find in production. The last statement grants EXECUTE on the stored procedure to the newly created user.

The Initial Test

In this next bit of code, I’m using the EXECUTE AS USER = N'owner_testing_user'; to simulate logging into the SQL Server as that user. If you like, you can simply open a new SSMS query window logged in as owner_testing_login, and run the EXEC and SELECT statements there.

The stored procedure executes without error, and returns results as expected. However, the SELECT statement results in an error:

Msg 229, Level 14, State 5, Line 57
The SELECT permission was denied on the object 'somedata', database 'owner_testing', schema 'dbo'.

That’s by design; we want the user to be able to retrieve data out of the table only via the stored procedure. If we let them select rows directly from the table, all hell might break loose™.

Uh-oh! Dynamic T-SQL!

Now, what if we want to make the @StartDate and @EndDate parameters optional? Perhaps the user wants to select every row that exists prior to January 1st, 2017 for example. Or sometimes, they want to get only today’s rows, via @StartDate >= '2019-07-30 00:00:00'. A good way to do that is with Dynamic T-SQL since it prevents what is known as kitchen-sink-coding where the T-SQL query contains every possible parameter, even if they are unused. Let’s reconstruct the stored procedure to accomplish that:

Now, let’s re-run the stored procedure:

We get that same error about SELECT permission denied:

Msg 229, Level 14, State 5, Line 102
The SELECT permission was denied on the object 'somedata', database 'owner_testing', schema 'dbo'.

That’s because the dynamic T-SQL is executed under the users security context, not that of the stored procedure.1

The Solution™

We can get around the problem by redefining the stored procedure to make use of the EXECUTE AS OWNER clause:

Note in the above code I am using parameters for everything that is passed in by the user, instead of simply appending the values directly to the @cmd string. That’s important because it prevents SQL Injection vulnerabilities. It’s doubly-important that you don’t create a SQL Injection vulnerability in a stored procedure that uses EXECUTE AS OWNER since the resulting dynamic T-SQL code will be executed in security context of the login that created the stored procedure, typically a sysadmin.2 But enough about that. Let’s run the proc now:

Now, we get the results we expect:

║ somedata_key ║      somechardata      ║        somedate         ║
║            1 ║ aaaaaaaaaaaaaaaaaaa... ║ 2019-07-25 12:58:18.233 ║

Success! This allows us to run Dynamic T-SQL from inside a stored procedure, without granting the caller rights to the underlying table.

Thanks for reading, and I hope you enjoyed this part of our series on T-SQL.

If you found this post helpful, would you consider sharing it on social media? That would really help me out!

1 – See Solomon’s comment below for a more technically accurate picture of what’s actually happening here.
2 – Code is not executed in the security context of the login that created the procedure, it is actually executed with the rights of the owner of the procedure, typically the schema, which in the case of the above example is dbo. See Solomon’s comment below for important details.