I had this requirement recently where I restored a database, and wanted to grant a particular user execute rights to all the stored procs in that db.
Not wanting to go through the tedious manual operation of granting permissions to each and stored proc out there, I found this tip in the newsgroups.
Kudos to Rick Sawtell for this clever SP
/* Procedure to Grant EXECUTE permissions on all
* user defined stored procedures in the database
* to the user defined role db_ProcExecuter
*/
CREATE PROC [dbo].[usp_UpdateProcPermissions] AS
DECLARE @ProcName varchar(128) -- Procedure Name
DECLARE @sql varchar(500)
DECLARE ProcCursor CURSOR FOR
SELECT Name
FROM sysobjects
WHERE TYPE = 'P'
AND Name NOT LIKE 'sp_%' -- remove system procs
--Loop through records and grant execute permissions
OPEN ProcCursor
FETCH NEXT FROM ProcCursor INTO @ProcName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql = 'GRANT Execute ON ' + @ProcName + ' TO [db_ProcExecutor]'
EXECUTE (@sql)
Print 'Updated permissions on ' + @ProcName
FETCH NEXT FROM ProcCursor INTO @ProcName
END
CLOSE ProcCursor
DEALLOCATE ProcCursor
HTH
Rick Sawtell
Tags: SQL, Stored Procs, Execute Permission, Stored Procedure