9

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?

8

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.

  • 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
  • 2
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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