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