1

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!

2

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.