Technical Article

SQL SCRIPT to grant DBC_Execute to all user SP

,

SQL SCRIPT to grant DBC_Execute to all user defined Stored Procs.
This SQL SCRIPT is handy specially after Stored Procs are DROPPED & CREATED; often users dont mention the SQL to grant DBC_Execute permission to the Store Procs.

This SQL SCRIPT will grant execute permission to DBC_Execute for all user defined Stored Procs. The SCRIPT can be slightly modified to add owner of the Stored Procs as well, which can address Stored Procs with any owner; currently it works for "dbo" owner only.

--The SQL 7.0/2000 Query for granting execute permission for all users Stored Proc to DBC_Execute; by Ramanuj on 02-Jan-2004
DECLARE @strStoredProcName varchar(255)
DECLARE @str   varchar(255)

DECLARE cur_grantExec_All CURSOR FOR
SELECT o.name 
FROM sysobjects o, sysusers u 
WHERE o.xtype = 'P' and o.status >= 0 and o.uid = u.uid 
ORDER by o.name

OPEN cur_grantExec_All 
FETCH NEXT FROM cur_grantExec_All INTO @strStoredProcName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @str = 'grant exec ON [' + @strStoredProcName + '] to DBC_Execute'
PRINT 'EXECUTING....' + @str
exec(@str)

FETCH NEXT FROM cur_grantExec_All INTO @strStoredProcName
END

CLOSE cur_grantExec_All
DEALLOCATE cur_grantExec_All

Rate

Share

Share

Rate