none
find all users in sql server

    Question

  • hi all,

    sql script to find all users in sql server with their permissions (active or non)

    thanks in advance

    Friday, April 20, 2012 8:57 AM

Answers

All replies

  • Select * From dbo.SysUsers



    Please, If answer match your requirement then set mark as answer

    Friday, April 20, 2012 9:36 AM
  • @ Dhaval , thx for ur response...my requirement is , i need all users list with their permissions (even users  who is inside a group).

    Friday, April 20, 2012 9:50 AM
  • select b.name as USERName, c.name as RoleName
    
    from dbo.sysmembers a 
    
     join dbo.sysusers b 
    
     on a.memberuid = b.uid 
     join dbo.sysusers c
    
    on a.groupuid = c.uid 



    Please, If answer match your requirement then set mark as answer

    Friday, April 20, 2012 10:33 AM
  • Yes, this wll give all the username and user role, so part1 is okay..my second part of the  question is to find the users names in a group with permissions (so i need to see whole user names and roles in a group) too.
    Friday, April 20, 2012 10:39 AM
  • may this help :

    http://www.sqlservercentral.com/Forums/Topic411310-338-1.aspx


    Please vote if you find this posting was helpful or Mark it as answered.

    Friday, April 20, 2012 10:43 AM
  • select b.name as USERName, c.name as RoleName
    
    from dbo.sysmembers a 
    
     join dbo.sysusers b 
    
     on a.memberuid = b.uid 
     join dbo.sysusers c
    
    on a.groupuid = c.uid 
    
    
    /*
    Security Audit Report
    1) List all access provisioned to a sql user or windows user/group directly 
    2) List all access provisioned to a sql user or windows user/group through a database or application role
    3) List all access provisioned to the public role
    
    Columns Returned:
    UserName        : SQL or Windows/Active Directory user cccount.  This could also be an Active Directory group.
    UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the 
                      SQL Server user account.
    DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
                      same as the server user.
    Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
                      on the user account, otherwise this will be the name of the role that the user is a member of.
    PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                      DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                      This value may not be populated for all roles.  Some built in roles have implicit permission
                      definitions.
    PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                      This value may not be populated for all roles.  Some built in roles have implicit permission
                      definitions.
    ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE, 
                      SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.   
                      This value may not be populated for all roles.  Some built in roles have implicit permission
                      definitions.          
    ObjectName      : Name of the object that the user/role is assigned permissions on.  
                      This value may not be populated for all roles.  Some built in roles have implicit permission
                      definitions.
    ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
                      is only populated if the object is a table, view or a table value function.                 
    */
    
    --List all access provisioned to a sql user or windows user/group directly 
    SELECT  
        [UserName] = CASE princ.[type] 
                        WHEN 'S' THEN princ.[name]
                        WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                     END,
        [UserType] = CASE princ.[type]
                        WHEN 'S' THEN 'SQL User'
                        WHEN 'U' THEN 'Windows User'
                     END,  
        [DatabaseUserName] = princ.[name],       
        [Role] = null,      
        [PermissionType] = perm.[permission_name],       
        [PermissionState] = perm.[state_desc],       
        [ObjectType] = obj.type_desc,--perm.[class_desc],       
        [ObjectName] = OBJECT_NAME(perm.major_id),
        [ColumnName] = col.[name]
    FROM    
        --database user
        sys.database_principals princ  
    LEFT JOIN
        --Login accounts
        sys.login_token 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]
    WHERE 
        princ.[type] in ('S','U')
    UNION
    --List all access provisioned to a sql user or windows user/group through a database or application role
    SELECT  
        [UserName] = CASE memberprinc.[type] 
                        WHEN 'S' THEN memberprinc.[name]
                        WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                     END,
        [UserType] = CASE memberprinc.[type]
                        WHEN 'S' THEN 'SQL User'
                        WHEN 'U' THEN 'Windows User'
                     END, 
        [DatabaseUserName] = memberprinc.[name],   
        [Role] = roleprinc.[name],      
        [PermissionType] = perm.[permission_name],       
        [PermissionState] = perm.[state_desc],       
        [ObjectType] = obj.type_desc,--perm.[class_desc],   
        [ObjectName] = OBJECT_NAME(perm.major_id),
        [ColumnName] = col.[name]
    FROM    
        --Role/member associations
        sys.database_role_members members
    JOIN
        --Roles
        sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
    JOIN
        --Role members (database users)
        sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
    LEFT JOIN
        --Login accounts
        sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
    LEFT JOIN        
        --Permissions
        sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[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]
    UNION
    --List all access provisioned to the public role, which everyone gets by default
    SELECT  
        [UserName] = '{All Users}',
        [UserType] = '{All Users}', 
        [DatabaseUserName] = '{All Users}',       
        [Role] = roleprinc.[name],      
        [PermissionType] = perm.[permission_name],       
        [PermissionState] = perm.[state_desc],       
        [ObjectType] = obj.type_desc,--perm.[class_desc],  
        [ObjectName] = OBJECT_NAME(perm.major_id),
        [ColumnName] = col.[name]
    FROM    
        --Roles
        sys.database_principals roleprinc
    LEFT JOIN        
        --Role permissions
        sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
    LEFT JOIN
        --Table columns
        sys.columns col on col.[object_id] = perm.major_id 
                        AND col.[column_id] = perm.[minor_id]                   
    JOIN 
        --All objects   
        sys.objects obj ON obj.[object_id] = perm.[major_id]
    WHERE
        --Only roles
        roleprinc.[type] = 'R' AND
        --Only public role
        roleprinc.[name] = 'public' AND
        --Only objects of ours, not the MS objects
        obj.is_ms_shipped = 0
    ORDER BY
        princ.[Name],
        OBJECT_NAME(perm.major_id),
        col.[name],
        perm.[permission_name],
        perm.[state_desc],
        obj.type_desc--perm.[class_desc] 



    Please, If answer match your requirement then set mark as answer

    Friday, April 20, 2012 10:46 AM
  • @ dhaval, i saw the same thing soem wehre in google, but for seom reaoson it is not wrking..
    Friday, April 20, 2012 10:55 AM
  • Thanks for the info...but this link not help my requrement

    Friday, April 20, 2012 10:56 AM
  • Hi,

    Please try this.

    SELECT UserName, 
    Max(CASE RoleName WHEN 'db_owner' THEN 'Y' ELSE 'N' END) AS db_owner,
    Max(CASE RoleName WHEN 'db_accessadmin ' THEN 'Y' ELSE 'N' END) AS db_accessadmin ,
    Max(CASE RoleName WHEN 'db_securityadmin' THEN 'Y' ELSE 'N' END) AS db_securityadmin,
    Max(CASE RoleName WHEN 'db_ddladmin' THEN 'Y' ELSE 'N' END) AS db_ddladmin,
    Max(CASE RoleName WHEN 'db_datareader' THEN 'Y' ELSE 'N' END) AS db_datareader,
    Max(CASE RoleName WHEN 'db_datawriter' THEN 'Y' ELSE 'N' END) AS db_datawriter,
    Max(CASE RoleName WHEN 'db_denydatareader' THEN 'Y' ELSE 'N' END) AS db_denydatareader,
    Max(CASE RoleName WHEN 'db_denydatawriter' THEN 'Y' ELSE 'N' END) AS db_denydatawriter
    from (
    select b.name as USERName, c.name as RoleName 
    from dbo.sysmembers a  join dbo.sysusers b 
     on a.memberuid = b.uid join dbo.sysusers c
    on a.groupuid = c.uid )s 
    Group by USERName 
    order by UserName

    Friday, April 20, 2012 11:44 AM
  • Thats great work Alok ! but not meeting my requirement....i need to know the all users with the permission who there  inside each group too.
    Friday, April 20, 2012 11:49 AM
  • it is still open.
    Saturday, April 21, 2012 11:43 AM
  • pls give a nice script alok
    Saturday, April 21, 2012 11:45 AM
  • Please try this.. 

    select distinct a.name as username,c.name as tablename,e.name as action
    from sysusers a,syspermissions b,sysobjects c,sysprotects d, master.dbo.spt_values e
    where a.uid=b.grantee
      and a.name like '%%' -- 'alok-pc\alok'
      and b.id=c.id
      and c.id=d.id
      and a.uid=d.uid
      and d.action = e.number
      and e.type = 'T'
    order by a.name,c.name,e.name

    Please, If answer match your requirement then set mark as answer

    Sunday, April 22, 2012 5:19 PM
  • try using sp_helplogins
    • Proposed as answer by Phadix Sunday, August 26, 2012 6:04 AM
    • Unproposed as answer by Phadix Sunday, August 26, 2012 6:05 AM
    • Proposed as answer by Jinu Varghese Wednesday, October 03, 2012 9:00 AM
    Saturday, August 25, 2012 10:23 AM
  • Hi jith

    If try sp_helpuser , you will see users , theirs Group names and permissions .

    also see MSDN ( ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/9c70b41d-ef4c-43df-92da-bd534c287ca1.htm ).

    • Edited by Phadix Thursday, September 06, 2012 7:27 AM
    • Proposed as answer by SQL IT Sunday, September 09, 2012 6:50 AM
    Sunday, August 26, 2012 6:09 AM