Get logins, databases users/roles and object level permission (T-SQL)

This script contains three code parts, one to get the result of server level logins and related roles, one to get the result of user databases’ users and related roles, one to get the result of object level permission of specific database.

 
 
 
 
 
3.9 Star
(28)
23,152 times
Add to favorites
Databases
7/29/2013
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • can any one verify my script?
    1 Posts | Last post February 03, 2016
    • https://gallery.technet.microsoft.com/Extract-Database-dfa53d5a
      
      i think this will get the permission of db level permission .
      
  • Granting access
    2 Posts | Last post January 25, 2016
    • Is it possible to get those who gave access to each database and other server resources to other users?
    • Durval,
      I guess not..
  • Does this fetch column level permissions ?
    2 Posts | Last post January 25, 2016
    • Does this fetch column level permissions ?
    • I guess not.
  • Script Error
    3 Posts | Last post January 25, 2016
    • Any idea why I get this error when running script? Thanks.
      
      Msg 102, Level 15, State 1, Line 3
      Incorrect syntax near '-'.
      Msg 102, Level 15, State 1, Line 3
      Incorrect syntax near '-'.
      Msg 102, Level 15, State 1, Line 3
      Incorrect syntax near '-'.
      
      Looks like the issue is with this piece but do not know what is wrong...
      
      SET @SQLStatement='
      SELECT ''?'' AS DBName,dp.name AS UserName,USER_NAME(drm.role_principal_id) AS AssociatedDBRole 
      FROM ?.sys.database_principals dp
      LEFT OUTER JOIN ?.sys.database_role_members drm
      ON dp.principal_id=drm.member_principal_id 
      WHERE dp.sid NOT IN (0x01) AND dp.sid IS NOT NULL AND dp.type NOT IN (''C'') AND dp.is_fixed_role <> 1 AND dp.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY DBName'
    • I had the same issue - caused by dashes in my database name. Change the SET line to this:
      
      SET @SQLStatement='
      SELECT ''?'' AS DBName,dp.name AS UserName,USER_NAME(drm.role_principal_id) AS AssociatedDBRole 
      FROM [?].sys.database_principals dp
      LEFT OUTER JOIN [?].sys.database_role_members drm
      ON dp.principal_id=drm.member_principal_id 
      WHERE dp.sid NOT IN (0x01) AND dp.sid IS NOT NULL AND dp.type NOT IN (''C'') AND dp.is_fixed_role <> 1 AND dp.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY DBName'
      
    • Are you setting the database correctly?
  • I want see which user having what right on each databse?
    1 Posts | Last post May 09, 2015
    • User ->
            Database -
                         Permission
  • DB object level permission script
    2 Posts | Last post March 24, 2015
    • In the third script (to fetch the DB object level permissions), as this is only on DB level, I think we need to remove the 'sp_MSforeachdb' execution to avoid the duplicate values in the output as it gives many duplicate records based on number of DB's exist on the instance.
      
      Only the below part to be executed in order to get the desired result. Please correct me if I am wrong.
      
      USE dbname
      SELECT Us.name AS username, Obj.name AS object,  dp.permission_name AS permission 
      FROM sys.database_permissions dp
      JOIN sys.sysusers Us 
      ON dp.grantee_principal_id = Us.uid 
      JOIN sys.sysobjects Obj
      ON dp.major_id = Obj.id order by Us.name
    • Or, you could more fully flesh it out so that it pulls back the object-level permissions in all of the databases by just making a slight adjustment to the code by commenting out the lines with the USE statement and adding in the database name to the result set:
      
           --Get objects permission of specified user database 
           --USE <Database Name>
           --GO
           DECLARE @Obj VARCHAR(4000)
           DECLARE @T_Obj TABLE (DBName SYSNAME, UserName SYSNAME, ObjectName SYSNAME, Permission   NVARCHAR(128))
           SET @Obj='
                     SELECT ''?'' AS DBName, Us.name AS username, Obj.name AS object,     dp.permission_name AS permission 
                     FROM sys.database_permissions dp
                     JOIN sys.sysusers Us 
                        ON dp.grantee_principal_id = Us.uid 
                     JOIN sys.sysobjects Obj
                        ON dp.major_id = Obj.id '
           INSERT @T_Obj 
           EXEC sp_MSforeachdb @Obj
           SELECT * FROM @T_Obj