12th June 00:58
complex sql script
I need to write a script that a dbo can run against a
database and it needs to do the following...
Create a new Role 'ABC'
Create another new Role 'DEF'
Grant execute permissions to Role 'ABC' to all stored
procs that begin with ABC.
Grant execute permissions to Role 'DEF' to all stored
procs that begin with DEF.
Grant execute permissions to Role 'ABC' to stored procs
that begin with DEF if it's being called by an ABC stored
Grant execute permissions to Role 'DEF' to stored procs
that begin with ABC if it's being called by a DEF stored
I've already sort of begun to write this script and have
passed the creation of the new roles and am now drudging
through the permission granting portion.
I've created a cursor to loop through the sysobjects
table to grab only stored procs. I check the first few
characters of each stored proc name to see if it's a ABC
or a DEF stored proc and grant permissions to it
accordingly. Then I get the id of that stored proc and
create another cursor to grab any dependent records in
the sysdepends table with that id and grab the depid.
With the depid I requery the sysobjects table to see if
it's a stored proc and, if so, I grant execute
permissions on it.
I've come to the conclusion that there's someone out
there with a better, less complicated solution to script
I'm asking for help folks.
Thanks in advance,
21st June 10:02
complex sql script
EXEC sp_addrole 'ABC'
EXEC sp_addrole 'DEF'
DECLARE @GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
'GRANT EXECUTE ON ' +
' TO ABC'
ROUTINE_NAME LIKE 'ABC%' AND
'IsMSShipped') = 0
WHILE 1 = 1
FETCH NEXT FROM GrantStatements INTO @GrantStatement
IF @@FETCH_STATUS = -1 BREAK
RAISERROR(@GrantStatement, 0, 1) WITH NOWAIT
EXEC sp_executesql @GrantStatement
Same technique as used above.
Permissions on indirectly executed procs are not needed as long as the
referencing and referenced procs have the same owner. Users only need
execute permissions on the directly referenced procs due to the unbroken
ownership chain. In other words, you won't have to bother if your procs
are all owned by 'dbo'.
Note that sysdepends data will be incomplete unless you (re)create
objects in the correct dependency order.
Hope this helps.
SQL Server MVP
SQL FAQ links (courtesy Neil Pike):