T-sql I need an awesome script that pulls all permissions for user

I need a query that will tell me if a user is part of another usergroup.. For example there may be a user group called "dq\analyst" and in this group there are 3 users.. I have a user named    "dg\rep1" and I somehow they can not login to a database even after giving them select permissions. I think they are a part of a group like "dq\analyst" that has been denied access to that database.. I have sooo many of these user groups and I dont know which group they could be a part of..Maybe I need a script that tells me which groups have been denied access.
LVL 1
cheryl9063Asked:
Who is Participating?
 
selva_konguConnect With a Mentor Commented:
try this script

SELECT [UserName] = ulogin.[name],
       [UserType]             = CASE princ.[type]
                         WHEN 'S' THEN 'SQL User'
                         WHEN 'U' THEN 'Windows User'
                         WHEN 'G' THEN 'Windows Group'
                    END,
       [DatabaseUserName]     = princ.[name],
       [Role]                 = NULL,
       [PermissionState]      = perm.[state_desc],
       [PermissionType]       = perm.[permission_name],
       [ObjectType]           = CASE perm.[class]
                           WHEN 1 THEN obj.type_desc -- Schema-contained objects
                           ELSE perm.[class_desc] -- Higher-level objects
                      END,
       [ObjectName]           = CASE perm.[class]
                           WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects
                           WHEN 3 THEN schem.[name] -- Schemas
                           WHEN 4 THEN imp.[name] -- Impersonations
                      END,
       [ColumnName]           = col.[name]
FROM   --database user
       sys.database_principals princ
       LEFT JOIN --Login accounts
            sys.server_principals ulogin
            ON  princ.[sid] = ulogin.[sid]
       LEFT JOIN --Permissions
            sys.database_permissions perm
            ON  perm.[grantee_principal_id] = princ.[principal_id]
       LEFT JOIN --Table columns
            sys.columns col
            ON  col.[object_id] = perm.major_id
            AND col.[column_id] = perm.[minor_id]
       LEFT JOIN sys.objects obj
            ON  perm.[major_id] = obj.[object_id]
       LEFT JOIN sys.schemas schem
            ON  schem.[schema_id] = perm.[major_id]
       LEFT JOIN sys.database_principals imp
            ON  imp.[principal_id] = perm.[major_id]
WHERE  princ.[type] IN ('S', 'U', 'G')
       AND -- No need for these system accounts
           princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
ORDER BY
       ulogin.[name],
       [UserType],
       [DatabaseUserName],
       [Role],
       [PermissionState],
       [PermissionType],
       [ObjectType],
       [ObjectName],
       [ColumnName] 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.