Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    1

    Unanswered: Looking for a script to audit user table rights

    Hello everyone,

    Does anyone know of a quick way to audit all users in a database and display their rights and permissions on a table level. I would hate to have to do it one user at a time. There has to be an easier way.

    I'm going through a Sarbanes Oxley audit and need to provide them this information.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Go over the code of sp_helprotect (using sp_helptext). You should be able to figure out how to run a query for all tables by using that code as a base.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This was taken directly from profiler and slightly modified:
    Code:
    select 'User ' + user_name(p.uid) + ' has ' +
       case p.[action]
    	  when 26  then 'REFERENCES'
    	  when 193 then 'SELECT'
    	  when 195 then 'INSERT'
    	  when 196 then 'DELETE'
    	  when 197 then 'UPDATE'
    	  when 224 then 'EXECUTE'
    	  else 'NON-OBJECT PERMISSION'
       end + ' permission assigned: ' +
       case p.protecttype
    	  when 204 then 'GRANT_W_GRANT'
    	  when 205 then 'GRANT'
    	  when 206 then 'DENY'
       end + ' for ' +
       case o.type
    	  when 'U' then 'table'
    	  when 'P' then 'procedure'
    	  else 'object'
       end + ' ' +
       user_name(o.uid) + '.' + o.name
       from dbo.sysprotects p (nolock), 
    	  dbo.sysobjects o (nolock),
    	  master.dbo.spt_values a (nolock)
    	  where o.id = p.id 
    	  and (( p.action in (193, 197) and ((p.columns & 1) = 1) ) 
    	  or ( p.action in (195, 196, 224, 26) ))
    	  and (convert(tinyint, substring( isnull(p.columns, 0x01), a.low, 1)) & a.high != 0)
    	  and a.type = N'P' and a.number = 0 order by o.name
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •