Posted by Erland Sommarskog on 04/25/07 21:46

Mark D Powell (Mark.Powell@eds.com) writes:
> I had thought that I had posted this yesterday but since I cannot find
> it here we go again.
>
> I have searched the archives but either the code I want is not posted
> or I missed it.
>
> I have a user create role in a database that I need to reproduce in
> another version of the database. Does anyone out there have the SQL
> or T-SQL necessary to generate all the grants to a role?

Not only you posted it yesterday, but I also replied yesterday. Let's
see if you are able to find the response this time:

The system table you need to look at is sysprotects. Here is a query
that gives the permissions for the most common commands. If you need
database permissions like CREATE TABLE, you will need to extend the
query below. I suspect that the id column has a special value in this
case; I have not investigated this myself.


SELECT CASE protecttype
WHEN 204 THEN 'GRANT'
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'DENY'
END + ' ' +
CASE action
WHEN 224 THEN 'EXECUTE'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 193 THEN 'SELECT'
WHEN 197 THEN 'UPDATE'
END + ' ON ' +
user_name(o.uid) + '.' + o.name +
' TO ' + user_name(p.uid)
FROM sysprotects p
JOIN sysobjects o ON p.id = o.id




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]