Re: How to Generate all grants to a user role? (SQL Server 2000)
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 24 Apr 2007 22:07:39 +0000 (UTC)
Mark D Powell (Mark.Powell@xxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- How to Generate all grants to a user role? (SQL Server 2000)
- From: Mark D Powell
- How to Generate all grants to a user role? (SQL Server 2000)
- Prev by Date: Re: sql script
- Next by Date: Re: Regarding sql error
- Previous by thread: How to Generate all grants to a user role? (SQL Server 2000)
- Next by thread: How to Generate all grants to a user role? (SQL Server 2000)
- Index(es):
Relevant Pages
|
