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?
Best Answer
None.
sp_executesqlexecutes 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.