Dynamic SQL Can Present a Security Problem

I was working on a project this week and stumbled across an issue I hadn’t seen before. The programmer had written stored procedures for every possible CRUD operation as well as all reporting requirements. Security was controlled by granting EXECUTE permissions only to appropriate stored procedures and by explicitly not granting permission to any tables or views within the database.

One of the procedures was getting a bad query plan and timing out. This is when I was called in. The procedure was performing a search based on an unknown number of up to 10 search parameters. The code was filled with many AND/OR combinations to account for the users’ ability to include any combination of search parameters. I found this procedure to be a prime candidate for dynamic SQL where I would create the select statement including only the search parameters entered by the user into the WHERE clause.

Immediately upon testing the users started to receive SELECT permission denied errors. It turns out that when you change to dynamic SQL and call your statement through sp_ExecuteSQL that the permissions are lost. Our options were to grant explicit select permission on the objects or to refactor the code such that it does not use dynamic SQL anymore. Next week I’ll be refactoring it again. 🙁

I set up this demo to prove the point. Feel free to download the code and try it yourself.

In this snippet I create a login that does not have permission to the table “noperms”. Then I create 2 procedures. The first simply selects from the noperms table and the second uses dynamic SQL to call the exact same statement.

create login nobody with password = 'test'
go
create database securitytest
go
use securitytest
go
create user nobody for login nobody
go
create table noperms(Result char(3))
insert into noperms values ('yes')
go
create procedure usp_standard as select * from noperms
go
grant execute on usp_standard to nobody
go
create procedure usp_dynamic as exec sp_executeSQL N'select * from noperms'
go
grant execute on usp_dynamic to nobody
go

Now open a new window on the same server and execute this script.

SET NOCOUNT ON
GO
use securitytest
go
--Expected to fail because we have not granted
select * from noperms
go
--Expected to succeed.
exec usp_standard
go
--This fails too....bummer
exec usp_dynamic
*/

Results from my execution.

As you can see, the select statement is denied as expected because select permission on the table was never granted to user “nobody”. Our standard procedure succeeds via the explicit EXECUTE permission that was granted to the procedure. The final call which uses dynamic SQL to perform the select is denied because the execute permission are not passed through to the dynamic SQL call.