none
SQL SERVER USERS

    Question

  • Hello Experts

    How can we list all sql server users(active or inactive) with the respective rights in a server? i was told to list all users and their rights on a hard copy, thank you as ususa!

    Monday, February 03, 2014 8:07 PM

Answers

  • Its very hard to differentiate active and inactive users. SQL Server logins may sit idle for a long period of time without use depending upon how frequently a database is accessed.

    I hope, the below query might give enough information what you are looking for.

    DECLARE @DBuser_sql VARCHAR(4000) 
    DECLARE @DBuser_table TABLE 
    (
    DBName VARCHAR(200), 
    UserName VARCHAR(250), 
    LoginType VARCHAR(500), 
    AssociatedDatabaseRole VARCHAR(200)
    ) 
    
    
    SET @DBuser_sql='
    SELECT ''?'' AS DBName,a.name AS Name,
    a.type_desc AS LoginType,
    USER_NAME(b.role_principal_id) AS AssociatedDatabaseRole 
    FROM ?.sys.database_principals a 
    LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id 
    LEFT OUTER JOIN ?.sys.server_role_members c ON a.principal_id=c.member_principal_id and a.principal_id=c.member_principal_id 
    WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') 
    AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') 
    ORDER BY Name'
    INSERT @DBuser_table 
    EXEC sp_MSforeachdb @command1=@dbuser_sql 
    
    --select * from @DBuser_table ORDER BY DBName
    
    SELECT DBName,UserName,LoginType,
    max(case when  AssociatedDatabaseRole ='db_owner' then '1' else '0' end )'db_owner',
    max(case when  AssociatedDatabaseRole ='db_securityadmin' then '1' else '0' end )'db_securityadmin',
    max(case when  AssociatedDatabaseRole ='db_accessadmin' then '1' else '0' end )'db_accessadmin',
    max(case when  AssociatedDatabaseRole ='db_backupoperator' then '1' else '0' end )'db_backupoperator',
    max(case when  AssociatedDatabaseRole ='db_ddladmin' then '1' else '0' end )'db_ddladmin',
    max(case when  AssociatedDatabaseRole ='db_datareader' then '1' else '0' end)'db_datareader',
    max(case when  AssociatedDatabaseRole ='db_datawriter' then '1' else '0' end) 'db_datawriter',
    max(case when  AssociatedDatabaseRole ='db_denydatawriter' then '1' else '0' end )'db_denydatawriter',
    max(case when  AssociatedDatabaseRole ='db_denydatareader' then '1' else '0' end )'db_denydatareader',
    max(case when  AssociatedDatabaseRole is NULL  then '1' else '0' end )'No Roles'
    FROM @DBuser_table --ORDER BY DBName 
    group by DBName,UserName,LoginType
    
    SELECT sp.name AS LoginName,sp.type_desc AS LoginType, sp.default_database_name AS DefaultDBName,slog.sysadmin AS SysAdmin,slog.securityadmin AS SecurityAdmin,slog.serveradmin AS ServerAdmin, slog.setupadmin AS SetupAdmin, slog.processadmin AS ProcessAdmin, slog.diskadmin AS DiskAdmin, slog.dbcreator AS DBCreator,slog.bulkadmin AS BulkAdmin
    FROM sys.server_principals sp  JOIN master..syslogins slog
    ON sp.sid=slog.sid 
    WHERE sp.type  <> 'R' AND sp.name NOT LIKE '##%'

    --Prashanth


    Monday, February 03, 2014 8:57 PM

All replies

  • Hello Experts

    How can we list all sql server users(active or inactive) with the respective rights in a server? i was told to list all users and their rights on a hard copy, thank you as ususa!

    Hello,

    See this links

    http://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database

    http://stackoverflow.com/questions/8471124/t-sql-to-list-all-the-user-mappings-with-database-roles-permissions-for-a-login


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, February 03, 2014 8:15 PM
  • -- selects out logins in instance

    select name, dbname from sys.syslogins

    -- selects out users and groups that has access to specific database

    -- ...must be run per database in instance

    select status, name, hasdbaccess, * from sys.sysusers

    where status in (4,12) -- 4 = group, 12 = login

    and hasdbaccess = 1

    I am sure that there must be a more handsome way of doing it....  :-)

    Monday, February 03, 2014 8:44 PM
  • Its very hard to differentiate active and inactive users. SQL Server logins may sit idle for a long period of time without use depending upon how frequently a database is accessed.

    I hope, the below query might give enough information what you are looking for.

    DECLARE @DBuser_sql VARCHAR(4000) 
    DECLARE @DBuser_table TABLE 
    (
    DBName VARCHAR(200), 
    UserName VARCHAR(250), 
    LoginType VARCHAR(500), 
    AssociatedDatabaseRole VARCHAR(200)
    ) 
    
    
    SET @DBuser_sql='
    SELECT ''?'' AS DBName,a.name AS Name,
    a.type_desc AS LoginType,
    USER_NAME(b.role_principal_id) AS AssociatedDatabaseRole 
    FROM ?.sys.database_principals a 
    LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id 
    LEFT OUTER JOIN ?.sys.server_role_members c ON a.principal_id=c.member_principal_id and a.principal_id=c.member_principal_id 
    WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') 
    AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') 
    ORDER BY Name'
    INSERT @DBuser_table 
    EXEC sp_MSforeachdb @command1=@dbuser_sql 
    
    --select * from @DBuser_table ORDER BY DBName
    
    SELECT DBName,UserName,LoginType,
    max(case when  AssociatedDatabaseRole ='db_owner' then '1' else '0' end )'db_owner',
    max(case when  AssociatedDatabaseRole ='db_securityadmin' then '1' else '0' end )'db_securityadmin',
    max(case when  AssociatedDatabaseRole ='db_accessadmin' then '1' else '0' end )'db_accessadmin',
    max(case when  AssociatedDatabaseRole ='db_backupoperator' then '1' else '0' end )'db_backupoperator',
    max(case when  AssociatedDatabaseRole ='db_ddladmin' then '1' else '0' end )'db_ddladmin',
    max(case when  AssociatedDatabaseRole ='db_datareader' then '1' else '0' end)'db_datareader',
    max(case when  AssociatedDatabaseRole ='db_datawriter' then '1' else '0' end) 'db_datawriter',
    max(case when  AssociatedDatabaseRole ='db_denydatawriter' then '1' else '0' end )'db_denydatawriter',
    max(case when  AssociatedDatabaseRole ='db_denydatareader' then '1' else '0' end )'db_denydatareader',
    max(case when  AssociatedDatabaseRole is NULL  then '1' else '0' end )'No Roles'
    FROM @DBuser_table --ORDER BY DBName 
    group by DBName,UserName,LoginType
    
    SELECT sp.name AS LoginName,sp.type_desc AS LoginType, sp.default_database_name AS DefaultDBName,slog.sysadmin AS SysAdmin,slog.securityadmin AS SecurityAdmin,slog.serveradmin AS ServerAdmin, slog.setupadmin AS SetupAdmin, slog.processadmin AS ProcessAdmin, slog.diskadmin AS DiskAdmin, slog.dbcreator AS DBCreator,slog.bulkadmin AS BulkAdmin
    FROM sys.server_principals sp  JOIN master..syslogins slog
    ON sp.sid=slog.sid 
    WHERE sp.type  <> 'R' AND sp.name NOT LIKE '##%'

    --Prashanth


    Monday, February 03, 2014 8:57 PM