Script to show users and permissions?

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

    Thanks and Regards,

    Felipe

  • 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

     

     

     

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply