Posted by Mark D Powell on 04/26/07 15:28

On Apr 25, 5:46 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Mark D Powell (Mark.Pow...@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, esq...@sommarskog.se
>
> 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 --

[Back to original message]