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

Mark D Powell (Mark.Powell@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, 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
.

Relevant Pages

  • Re: No db access after publishing web site
    ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Newbie to security
    ... Use sp_grantdbaccess to grant access to the database. ... databases when you are new to security. ... Microsoft SQL Server 2000 SP3 Security Features and Best ...
    (microsoft.public.sqlserver.security)
  • Re: Install MSDE w/ MSDE Depl.Toolkit. What permissions when using Win Auth?
    ... > have my SQL database sitting on my server. ... > the local MSDE database. ... on your SQL Server you grant login to that group and grant db access too... ...
    (microsoft.public.sqlserver.msde)
  • Re: SQL Server Express
    ... GRANT them permissions to do so with the GRANT command. ... GRANT CREATE DATABASE on DATABASE::xxx to SAM ... Yes, I am very familiar with configuring specific SQL Server accounts for access, and I am having no real problems in this area. ... really a good way to block administrator access without making your database pretty hard to administer. ...
    (microsoft.public.sqlserver.msde)
  • Re: How to protect SQL Server Express database from reverse engineerin
    ... prevent anyone from reverse engineering our database. ... I say within reasonable limits, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)