Permissions And Dynamic SQL

Eric Blinn shows that dynamic SQL in stored procedures changes the security paradigm a bit:

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.

The best solution here would probably be to use a certificate to sign the procedure and give that certificate user rights to select from the tables used in dynamic SQL.

Related Posts

Aggregations And Always Encrypted

Monica Rathbun finds trouble with Always Encrypted: The real challenges started when the client began to test their application code. The first thing we hit was triggers. The table had several insert triggers associated with the columns that were now encrypted. Since the data was now encrypted the insert triggers would fail. Again, we lucked […]

Read More

Storing Credentials For Containers

Andrew Pruski shows how to store a credential using Powershell and pass it into a Docker container: I work with SQL Server in containers pretty much exclusively when testing code and one of my real bug bears is that SQL Server in containers does not support Windows authentication (unless you’re using Windocks). So when I’m working […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

January 2018
MTWTFSS
« Dec  
1234567
891011121314
15161718192021
22232425262728
293031