Sql-server – Delete SQL Server 2008 R2 server logins mapped to offline databases

loginssql-server-2008-r2

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!

Best Answer

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

Related Question