Permission to access Person.Person is implied within the scope of the stored procedure, i.e. In Example 1 any database user granted permission to execute the stored procedure Person.GetPersonByLastName will be able to retrieve data from Person.Person without being explicitly granted SELECT-permission on Person.Person. WHERE object_id = OBJECT_ID(N'Person.GetPersonByLastName')ĭROP PROCEDURE Person.GetPersonByLastName ĬREATE PROCEDURE Person.GetPersonByLastName dbo.Name)Įxample 1 - stored procedure definition uses only declared SQL Let’s start with a simple example of a stored procedure that could exist within the Person schema of the AdventureWorks2012 sample database that selects from a table, also in the Person schema:
If you want to follow along by implementing the code samples in this article in your own environment so you can test the results you'll need the following: My favorite use of dynamic SQL, which I learned from Microsoft Certified Master: SQL Server 2008 and SQL Server MVP Gail Shaw, is a very elegant way to solve the problem presented by catch-all queries, and so I’ll be coding to solve a simple version of this problem case in the examples throughout this article.
This article picks up after you have already decided to employ dynamic SQL to show how to maintain a higher level of database security by also employing Loginless Database Users along with the stored procedures that contain dynamic SQL. I have chosen to leave the topics of database performance and design as largely out of the scope of this article and will leave the decision of whether or not to employ dynamic SQL to solve the problem-at-hand to the reader. While there are some use cases where employing dynamic SQL is not only justified but is a better choice than declared SQL, there are many more when it is a poor choice.
At minimum, when recommended, it should come with disclaimers about SQL injection, data security, plan cache re-use, database performance and database design.
How can I grant a Database User permission to execute a stored procedure that makes use of dynamic SQL while also preventing that User from connecting to the database using a tool like SQL Server Management Studio and directly accessing data in the tables referenced within the dynamic SQL? Backgroundĭynamic SQL has a somewhat bad reputation in some SQL Server professional circles and recommending its use can be a provocative topic of discussion for some. To summarize, this article aims to answer the following question: Target Audienceĭevelopers or database administrators writing new or supporting existing stored procedures that make use of dynamic SQL where the Database User granted permission to execute the stored procedure should not also be granted direct access the tables referenced within the dynamic SQL. In this article we will explore how to leverage a SQL Server feature I informally call Loginless Database Users to maintain data security and preserve interface when dynamic SQL is being used within a stored procedure.