steve moreno

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
proc.

Grant execute permissions to Role 'DEF' to stored procs
that begin with ABC if it's being called by a DEF stored
proc.

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
out.

I'm asking for help folks.

Thanks in advance,
Steve.

dan guzman

EXEC sp_addrole 'ABC'

EXEC sp_addrole 'DEF'


DECLARE @GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
'.' +
QUOTENAME(ROUTINE_NAME) +
' TO ABC'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_NAME LIKE 'ABC%' AND
OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(ROUTINE_SCHEMA) +
'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements INTO @GrantStatement
IF @@FETCH_STATUS = -1 BREAK
RAISERROR(@GrantStatement, 0, 1) WITH NOWAIT
EXEC sp_executesql @GrantStatement
END
CLOSE GrantStatements
DEALLOCATE GrantStatements


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.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------