Posted by Erland Sommarskog on 04/24/07 22:07

Mark D Powell (Mark.Powell@eds.com) writes:
> Using SQL Server 2000 I need to extract all the grants made to a user
> created role so that I can recreate the role in another version of a
> database and there are more grants than I want to do by hand. Does
> anyone have SQL or T-SQL to perform this task that they would be
> willing to share?
>
> I tried searching the archives but either I missed the post or what I
> want isn't posted.

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]