I have circumstances that a server can host many databases and a lot of them get offline and transfered elsewhere from time to time. This causes lots of server logins to be created and stay there after the transfer. I need a script to find all the server logins that are mapped to offline or non existing databases so I can clean them.
Thank you!
Here is a script to identify orphaned logins.
SELECT SL.name, SL.dbname
, 'USE [master];
ALTER LOGIN [' + SL.name + '] WITH DEFAULT_DATABASE=[master];
CREATE USER [' + SL.name + '] FOR LOGIN [' + SL.name + ']
WITH DEFAULT_SCHEMA = [dbo];' AS SQL_command
FROM sys.[syslogins] SL
LEFT JOIN sys.[databases] SD
ON SL.[dbname] = SD.[name]
WHERE SD.name IS NULL
ORDER BY SL.[name], SL.[dbname];
The full article can be found on http://sqlmag.com/database-administration/identifying-orphaned-logins
