Granting VIEW DEFINITION and EXECUTE Permissions on All Stored Procedures in SQL Server

Managing permissions effectively in SQL Server is crucial to maintaining security and access control. One common requirement is to grant users or groups the ability to view the definitions of stored procedures or execute them without giving broader privileges.

In this post, we'll share SQL scripts that automate granting VIEW DEFINITION and EXECUTE permissions on all stored procedures in a given database.

Granting VIEW DEFINITION Access

The VIEW DEFINITION permission allows users to see the stored procedure’s definition without modifying it. Use the following script to generate and execute GRANT VIEW DEFINITION statements for all stored procedures:

SELECT 'GRANT VIEW DEFINITION ON ' + QUOTENAME(specific_schema) + '.' + QUOTENAME(specific_name) + ' TO [Domain\\Ajeyudu]'

FROM INFORMATION_SCHEMA.ROUTINES

WHERE routine_type = 'PROCEDURE'

Note: Replace [Domain\\Ajeyudu] with the actual user or group to whom you want to grant permissions.

After running the above query, copy the generated GRANT VIEW DEFINITION statements and execute them.

Granting EXECUTE Access

To allow users to execute all stored procedures without requiring other permissions, use the script below:

SELECT 'GRANT EXECUTE ON ' + QUOTENAME(specific_schema) + '.' + QUOTENAME(specific_name) + ' TO [Domain\\Ajeyudu]'

FROM INFORMATION_SCHEMA.ROUTINES

WHERE routine_type = 'PROCEDURE'

Run the generated commands to apply EXECUTE permissions to the specified user or group.

Automating the Permission Granting

For larger databases, automating permission assignments via a dynamic SQL approach can be beneficial. Use the following script to execute the generated GRANT statements dynamically:

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + 'GRANT VIEW DEFINITION ON ' + QUOTENAME(specific_schema) + '.' + QUOTENAME(specific_name) + ' TO [Domain\\Ajeyudu];' + CHAR(13)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE routine_type = 'PROCEDURE'

EXEC sp_executesql @sql

Similarly, modify the script for EXECUTE permissions:

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + 'GRANT EXECUTE ON ' + QUOTENAME(specific_schema) + '.' + QUOTENAME(specific_name) + ' TO [Domain\\Ajeyudu];' + CHAR(13)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE routine_type = 'PROCEDURE'

EXEC sp_executesql @sql

By using dynamic SQL, these scripts can directly apply permissions without requiring manual execution of individual statements.