How to Generate all grants to a user role? (SQL Server 2000)

[Mark D Powell]

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?

-- Mark D Powell --

[Erland Sommarskog]

Mark D Powell (Ma*********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Mark D Powell]

On Apr 25, 5:46 pm, Erland Sommarskog <[e-mail]:

Mark D Powell ([e-mail]) 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, [e-mail]

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

Yes, today I can find the thread. I am viewing the group via google
so I actually tried the Advanced Search option to hunt up the thread
but it came up empty.

Your query is much nicer that what I was about to resort to doing.
Using the EM generate code option under all tasks for a database I had
generated the DDL to a file. I was about ready to start filtering out
all the object DDL so that I would only have the grants left.

Modifying your query to work for a specific user should be easier.

Thanks.

-- Mark D Powell --