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

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

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 --

.

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: complex filter and calculations in access
    ... switch to SQL view. ... query by switching to datasheet view, ... of your database using the from address in this post. ... pre-op infections yes/no ...
    (microsoft.public.access.queries)
  • Re: A little more meat this week
    ... implementation with a query processor capable of returning a result set ... then we can call it a result bag (SQL ... I'm looking at the data model and not database tools at this ... for a s/w developer and a s/w developer simplifies for the end user). ...
    (comp.databases.pick)
  • Re: complex filter and calculations in access
    ... when using a subquery on the same table as the main query, ... switch to SQL view. ... of your database using the from address in this post. ...
    (microsoft.public.access.queries)
  • Re: A little more meat this week
    ... said data includes lists." ... implementation with a query processor capable of returning a result set ... then we can call it a result bag (SQL ... I'm looking at the data model and not database tools at this ...
    (comp.databases.pick)