Using system tables to script permissions

I recently had call to set execute permissions on a database with over 200 procedures belonging to over 35 schemas (don’t ask .. its not my design).

As all of the schemas shared a common prefix (af2) it was relatively straight forward to write a query to give each of these procedures execute permissions to a particular role. This may not be the most elegant solution (I’m not saying it is), but it was a one off and it ran in a few seconds so I’m happy with it. It may be of use to others (in its original or altered form).

declare @schema nvarchar(max)
declare @role nvarchar(max)
declare @proc nvarchar(max)
declare @sql nvarchar(max)
set @role = 'someRole'

declare curSchemas cursor for
    select [name] from sys.schemas
    where [name] like 'af2%'

open curSchemas
fetch next from curSchemas into @schema

while @@fetch_status = 0 begin

    declare curProcs cursor for
    select p.name from sys.procedures p
        inner join sys.schemas s
        on p.schema_id = s.schema_id
        where s.name = @schema

    open curProcs
    fetch next from curProcs into @proc

    while @@fetch_status = 0 begin

        set @sql = 'grant execute on ' + @schema + '.' + @proc + ' to ' + @role
        exec sp_executesql @sql

        fetch next from curProcs into @proc

    end

    close curProcs
    deallocate curProcs

    fetch next from curSchemas into @schema

end

close curSchemas
deallocate curSchemas
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s