Script to show users and permissions?

• Felipe Venancio

  Mr or Mrs. 500

  Points: 539

  More actions

  March 11, 2005 at 12:53 pm

  #92629

  Does somenone knows any script to show the users and permissions of all databases???

  Thanks and Regards,

  Felipe

• DBAEngg

  Old Hand

  Points: 394

  More actions

  March 11, 2005 at 1:04 pm

  #546714

  Try this:

  --The procedure below will give you information about the type of access which the  users has been granted. 

   

   

  select user_name(b.uid)as username ,object_name(id) as objectname , case when action = 26  then 'References'

        when action = 178 then 'CREATE FUNCTION'

  when action = 193 then 'SELECT'

  when action = 195 then 'INSERT'

  when action = 196 then 'DELETE'

  when action = 197 then 'UPDATE'

  when action = 198 then 'CREATE TABLE'

  when action = 203 then 'CREATE DATABASE'

  when action = 207 then 'CREATE VIEW'

  when action = 222 then 'CREATE PROCEDURE'

  when action = 224 then 'EXECUTE'

  when action = 228 then 'BACKUP DATABASE'

  when action = 233 then 'CREATE DEFAULT'

  when action = 235 then 'BACKUP LOG'

  when action = 236 then 'CREATE RULE' end as Permission , case when protecttype = 204 then 'GRANT_W_GRANT'

        when protecttype = 205 then 'GRANT'

        when protecttype = 206 then 'REVOKE'  end as permissiontype From sysusers a join sysprotects  b on a.uid = b.uid and objectproperty(b.id,'IsSystemTable') = 0 order by Permission