Find all uses without User Mappings

Question

I am looking for a query to list all users without a mapping to a database. I have over 500 users and I think that there are some SQL or Windows accounts that are not needed any longer.

Answer 1

Below code will give all the Logins with Database Mapping. It gives all the available logins. You can remove the mapping part.

--Logins and Permissions
DECLARE @dbname VARCHAR(50)-- database name  
DECLARE @SQL VARCHAR(MAX)

CREATE TABLE #LoginPermissions
(
ServerName Varchar(50),
DBName Varchar(50),
LoginType Varchar(10),
srvLogin Varchar(100),
srvRole Varchar(100),
dbUser Varchar(100),
dbRole Varchar(100)
)

DECLARE db_cursor CURSOR FOR  
SELECT name FROM MASTER.dbo.sysdatabases

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @dbname   

WHILE @@FETCH_STATUS = 0   
BEGIN   

SET @SQL = '
Insert Into #LoginPermissions
select 
@@SERVERNAME AS ServerName,'''
+ @dbname + ''' AS DBName,
[Login Type]=
case sp.type
when ''u'' then ''WINDOWS''
when ''s'' then ''SQL LOGIN''
when ''g'' then ''GROUP''
end,
convert(char(45),sp.name) as srvLogin, 
convert(char(45),sp2.name) as srvRole,
convert(char(25),dbp.name) as dbUser,
case when convert(char(25),dbp2.name) IS NULL then ''Public'' ELSE convert(char(25),dbp2.name) end as dbRole

from ' + 
@dbname + '.sys.server_principals as sp join '
+ @dbname + '.sys.database_principals as dbp on sp.sid=dbp.sid left join '
+ @dbname + '.sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id left join '
+ @dbname + '.sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join '
+ @dbname + '.sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join '
+ @dbname + '.sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id'

EXECUTE (@SQL)
       FETCH NEXT FROM db_cursor INTO @dbname   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

SELECT * FROM
(
Select distinct srvLogin, LoginType, 
case when srvRole = 'sysadmin' then '' ELSE DBName END as DBName, ServerName, ISNULL(srvRole, '') as srvRole,
case when srvRole = 'sysadmin' then '' ELSE dbRole END as DBRole from #LoginPermissions

UNION ALL

Select name, 
[Login Type]=
case [type]
when 'u' then 'WINDOWS'
when 's' then 'SQL LOGIN'
when 'g' then 'GROUP'
end, '', @@SERVERNAME,
CASE WHEN IS_SRVROLEMEMBER ('sysadmin',name) = 1 THEN 'sysadmin'
     WHEN IS_SRVROLEMEMBER ('serveradmin',name) = 1 THEN 'serveradmin'
     ELSE ''
END AS SrvRole, ''
from sys.server_principals where convert(char(45),name) not in (select distinct srvLogin from #LoginPermissions)
) x
Where LoginType IS NOT NULL
order by ISNULL(srvRole, '') desc, srvLogin

DROP TABLE #LoginPermissions