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
]]>