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