By default, in SQL Server, the
[public] role has
EXECUTE rights on
However, I’ve inherited a database server where the previous DBA has revoked the
EXECUTE right on
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?
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.