Using Stored Procedures to Manage SQL Server Security

5. To help automate this process you can run the following stored procedure. This will automatically add Execute permissions to all non-system/VSS procedures in your database. You can tweak this code to process only a subset of your stored procedures.

CREATE PROCEDURE prc_gen_CreateGrants AS

/* ------------------------------------------------------------
PROCEDURE: prc_gen_CreateGrants

DESCRIPTION: Grants Execute permissions on all procs in database
for Login MyLogin

AUTHOR: Brian Lockwood 3/15/00 5:38:48 PM
------------------------------------------------------------ */

DECLARE @ExecSQL varchar(100)

DECLARE curGrants CURSOR FOR

SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs

OPEN curGrants

FETCH NEXT FROM curGrants

INTO @ExecSQL

WHILE @@FETCH_STATUS = 0

BEGIN -- this will loop thru all your own procs and grant Execute privileges on each one

Exec(@ExecSQL)
IF @@ERROR <> 0
BEGIN
RETURN 1 -- return 1 if there is an error
END

Print @ExecSQL

FETCH NEXT FROM curGrants INTO @ExecSQL

END

CLOSE curGrants
DEALLOCATE curGrants

Published with the express written permission of the author. Copyright 2002