How to Find SQL Logins with NO Database Mappings

SQL Server is one of the most common attack surphases for would-be hackers, so it’s important to maintain a good security practices over the logins which have access into your environment.

The following script checks your SQL Server for any accounts which do NOT have any databases mapped to them. While this isn’t a conclusive indicator that the login is no longer in use, it does provide a good starting point for further investigation into whether these accounts are in fact still required or not.

USE MASTER; 
CREATE TABLE #dbusers ( 
sid VARBINARY(85)) 
EXEC sp_MSforeachdb 
'insert #dbusers select sid from [?].sys.database_principals where type != ''R''' 
SELECT name 
FROM   sys.server_principals 
WHERE  sid IN (SELECT sid 
FROM   sys.server_principals 
WHERE  TYPE != 'R' 
AND name NOT LIKE ('##%##') 
EXCEPT 
SELECT DISTINCT sid 
FROM   #dbusers) 
GO 
DROP TABLE #dbusers