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

Pages: 1 2




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |