![]() ![]() 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |