Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

By default, in SQL Server, the [public] role has EXECUTE rights on sp_executesql.

However, I've inherited a database server where the previous DBA has revoked the EXECUTE right on sp_executesql.

As a temporary workaround, I've been granting EXECUTE rights to sp_executesql on an as-needed basis (through a role in the master database). But this is beginning to be a maintenance pain.

If I grant EXECUTE back to public, are there any repercussions I need to be aware of?

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

None. sp_executesql executes SQL, under exactly the same context and privileges as the original caller would execute the same SQL. There are many, many, cases when dynamic SQL is unavoidable.

share|improve this answer
3  
-1 because the answer does not indicate that microsoft notes that there can be a security risk msdn.microsoft.com/en-us/library/ms188001(v=sql.105).aspx –  miracle173 Apr 10 '12 at 17:38
    
@miracle173 - To be fair it is badly written dynamic SQL that is the security risk not sp_executesql. Assuming a dynamic SQL requirement then disabling sp_executesql might actually increase the risk as at least that allows you to parameterise the queries (unlike EXEC) –  Martin Smith Apr 11 '12 at 6:56
1  
While the risk of sql injection can never be underestimated, disabling sp_executesql is not the appropriate mitigation. The overwhelming majority of SQL injection errors occur in the client when the SQL statement is build 'by hand' by concatenating pieces of SQL with input variables and then executes 'as is'. For all those cases sp_executesql being disabled helps nothing. –  Remus Rusanu Apr 11 '12 at 7:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.