locked
List all users with no permissions? RRS feed

  • Question

  • Hello, is there a way to get a list of all users within a DB that have not been assigned permissions/roles?

    Thanks in advance.

    Tuesday, June 17, 2014 5:15 PM

Answers

  • There may be a chance this users may have fixed server role.

    to get complete list you can try the below query

    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 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
    group by DBName,UserName,LoginType
    
    -----Server Roles
    
    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 '##%'

    OR

    You can also the refer below link

    http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx

    --Prashanth


    Tuesday, June 17, 2014 5:23 PM
  • Below query may help you. ----------------------- SELECT pr.principal_id, pr.name, pr.type_desc, --pr.authentication_type_desc, pe.state_desc, pe.permission_name, s.name + '.' + o.name AS ObjectName FROM sys.database_principals AS pr JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id JOIN sys.objects AS o ON pe.major_id = o.object_id JOIN sys.schemas AS s ON o.schema_id = s.schema_id where pe.state_desc <> 'GRANT' --getting only deny permission.

    Santosh Singh

    • Marked as answer by tracycai Wednesday, June 25, 2014 1:25 AM
    Tuesday, June 17, 2014 5:39 PM
  • You may be interested in my SQL Server TechNet Wiki article on Effective Database Engine Permissions http://social.technet.microsoft.com/wiki/contents/articles/15180.effective-database-engine-permissions.aspx It includes an explanation of all the places permissions can come from, and some queries to find them all.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by tracycai Wednesday, June 25, 2014 1:25 AM
    Thursday, June 19, 2014 3:04 PM

All replies

  • There may be a chance this users may have fixed server role.

    to get complete list you can try the below query

    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 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
    group by DBName,UserName,LoginType
    
    -----Server Roles
    
    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 '##%'

    OR

    You can also the refer below link

    http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx

    --Prashanth


    Tuesday, June 17, 2014 5:23 PM
  • Below query may help you. ----------------------- SELECT pr.principal_id, pr.name, pr.type_desc, --pr.authentication_type_desc, pe.state_desc, pe.permission_name, s.name + '.' + o.name AS ObjectName FROM sys.database_principals AS pr JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id JOIN sys.objects AS o ON pe.major_id = o.object_id JOIN sys.schemas AS s ON o.schema_id = s.schema_id where pe.state_desc <> 'GRANT' --getting only deny permission.

    Santosh Singh

    • Marked as answer by tracycai Wednesday, June 25, 2014 1:25 AM
    Tuesday, June 17, 2014 5:39 PM
  • You may be interested in my SQL Server TechNet Wiki article on Effective Database Engine Permissions http://social.technet.microsoft.com/wiki/contents/articles/15180.effective-database-engine-permissions.aspx It includes an explanation of all the places permissions can come from, and some queries to find them all.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by tracycai Wednesday, June 25, 2014 1:25 AM
    Thursday, June 19, 2014 3:04 PM