Wednesday, September 22, 2010

Ms Sql Server Grant Execute against sub-set of stored procs

Thanks to Karlson for this -
Here's a script that can grant execute (or whatever you wish to grant) to a set of stored procs based on a prefix in the stored proc name:

Set nocount on
Declare @proc_name as nvarchar(max)

DECLARE procs CURSOR
     FOR Select [name] FROM    sysobjects WHERE [name] like '[sp prefix here]%' AND xtype = 'P'

OPEN procs

FETCH NEXT FROM procs
INTO @proc_name

WHILE @@FETCH_STATUS = 0
BEGIN

    exec('Grant Execute ON ' + @proc_name + ' TO [roleName here]')

    FETCH NEXT FROM procs
    INTO @proc_name
END
CLOSE procs;
DEALLOCATE procs;
Set nocount off

No comments: