Identifying Orphaned Logins

Security. . . It's Like Juggling Chainsaws

We've all experienced the joy of working with consolidated SQL Server instances and dealing with the detrius of database migrations and upgrades: Obsolete SQL Agent Jobs, unnecessary backups sitting in the file system, and of course, orphan logins. It's this last issue that I dig into in this article: Identifying and correcting for orphan logins on your SQL Server instances.

Identifying the Orphan Logins

Orphaned logins exist when you've dropped the default database assigned for a specific login (SQL or Active Directory-based). Depending on the connection method used by the application(s) associated with the solution involved, this can lead to a connection failure. This usually arises when a login is associated with multiple databases on the same SQL Server instance and one of these databases—the one associated with the default database assigned to the login—is dropped. This frequently occurs on consolidated instances where logins are used across applications being supported from the databases hosted there.

Related: Clean Up SQL Server Logins and Users with PowerShell

You really have two options once these poor little orphans are identified: Drop the logins if they're no longer needed on this instance or reset the default database to a database that exists. In the example below, I'm using the master database to provide that functionality; you may choose something else for your environment. You can make this selection reusable and customizable by replacing the WITH DEFAULT_DATABASE=[master] code with a Template parameter (covered in a previous article, Introduction to Using the Template Explorer in SQL Server Management Studio) as follows: WITH DEFAULT_DATABASE=[].

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] SLLEFT JOIN sys.[databases] SDON SL.[dbname] = SD.[name]WHERE SD.name IS NULLORDER BY SL.[name], SL.[dbname];

The results from my test environment are displayed below; two logins exist where their default database has been dropped. There is also ad-hoc T-SQL generation in the third column that changes the default database to master and creates a database user with public role membership only.

Let's take a closer look at that third column. Grid results doesn't do the values justice. What you'll see is that the values provide ad-hoc T-SQL for re-assigning the login to a default database that never is deleted: Master. Of course, this is only if you need to keep the login around because it may have rights in multiple databases; not just the database that was dropped at some point.

USE [master];ALTER LOGIN [app_caribbean] WITH DEFAULT_DATABASE=[master];CREATE USER [app_caribbean] FOR LOGIN [app_caribbean] WITH DEFAULT_SCHEMA = [dbo];USE [master];ALTER LOGIN [app_mediterranean] WITH DEFAULT_DATABASE=[master];CREATE USER [app_mediterranean] FOR LOGIN [app_mediterranean] WITH DEFAULT_SCHEMA = [dbo];

If that's not the case, then just schedule and drop the unnecessary login. I also always recommend double-checking the properties for the login to ensure it has no additional rights in other databases, just in case. DBAs are cautious by nature after all!