dropUnusedLogins

DECLARE @username sysname
DECLARE @objname  sysname
DECLARE @found integer
DECLARE @sql nvarchar(4000)

SET     @username = ' '
WHILE@username IS NOT NULL
BEGIN
SELECT @username = MIN(name)
FROM master.dbo.syslogins 
WHERE sysadmin = 0
AND securityadmin = 0
AND serveradmin = 0
AND setupadmin = 0
AND processadmin = 0
AND diskadmin = 0
AND dbcreator = 0
AND bulkadmin = 0
AND name > @username
-- this is the list of non system logins
-- ids in server roles may not have corresponding users
-- any database but they should not be rremoved
SET  @found = 0

IF @username IS NOT NULL
BEGIN
--  now we search through each non system database 
--  to see if this login has database access
SET  @objname = ''
WHILE@objname IS NOT NULL
BEGIN
SELECT @objname = MIN( name ) FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
AND name > @objname

IF@objname IS NOT NULL
BEGIN
SET @sql = N'SELECT @found = COUNT(*) FROM ' + @objname 
+ N'.dbo.sysusers s JOIN master.dbo.sysxlogins x 
ON s.sid = x.sid WHERE hasdbaccess = 1 AND x.name = '''+ @username + ''''

EXEC sp_executesql @sql,N'@found Int OUTPUT',@found OUTPUT
--SELECT @found, @objname, @username
IF @found IS NOT NULL AND @found > 0 
SET @objname = 'zzzzz'  -- terminate as a corresponding user has been found
END
END
IF @found = 0 
BEGIN
EXEC sp_droplogin @username
SELECT @username + ' was removed ' + CONVERT(varchar(23),getdate())
END
END
END