Identifying Logins with Default Database Connectivity Issues in SQL Server

In an earlier article I wrote of "orphaned logins": logins that have a sid (the unique identifier for security principals on SQL Server instances) mismatch with an associated database user. I had a reader ask if there was a process for identifying yet another aspect of failed connectivity: logins where the associated default database has been dropped.

"Thanks for the post,
Can i get any script to find the Orphan Logins(Orphaned logins exist when you've dropped the default database assigned for a specific login)

i am working on a Test environment, here we are creating one login for one database as per the requirement but after dropping the databases logins still existed in the server .

is there any script to find these logins and dropping"

Related

Identifying Orphaned Logins

What Every Accidental DBA Needs to Know About the Basics of SQL Server Security

Simulating a Missing Default Database Situation

One may assume this is a simple enough query: run a SELECT statement against sys.server_principals for any login where the default database is NULL. It's not that simple as there is no underlying cleanup - nor foreign key reference - between sys.databases (the system view for databases on a SQL Server instance) and sys.server_principals (the system view for login and server-level roles in Microsoft SQL Server.)

USE [master]GO-------------------------------------------/*CREATE LOGIN. SET DEFAULT DATABASE. CREATE USER IN DEFAULT DATABASE*/-------------------------------------------CREATE LOGIN [foofoofoo] WITH PASSWORD=N'foofoofoo', DEFAULT_DATABASE=[foo], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGOUSE [foo]GOCREATE USER [foofoofoo] FOR LOGIN [foofoofoo] WITH DEFAULT_SCHEMA=[dbo]GO-- CREATE TWO ADDITIONAL USERS FOR foofoofoo LOGIN FOR USE LATER IN ARTICLE:USE [SQL_Cruise]GOCREATE USER [foofoofoo] FOR LOGIN [foofoofoo] WITH DEFAULT_SCHEMA=[dbo]GOUSE [iDBA]GOCREATE USER [foofoofoo] FOR LOGIN [foofoofoo] WITH DEFAULT_SCHEMA=[dbo]GO-------------------------------------------/*IDENTIFY LOGINS WITH DEFAULT DATABASE ISSUES*/-------------------------------------------SELECT name FROM sys.server_principals WHERE default_database_name IS NULL AND type_desc != 'SERVER_ROLE' ORDER BY name;-------------------------------------------/*DROP THE DEFAULT DATABASE AND CHECK AGAIN*/-------------------------------------------DROP DATABASE foo;SELECT name FROM sys.server_principals WHERE default_database_name IS NULL AND type_desc != 'SERVER_ROLE' ORDER BY name;

What one would expect to see is the default database for this user report as NULL. Not so however:

This means that a cursory review of the state of logins would show no issues when the DBA starts to assess connection issues: particularly if the user is querying a different database than their assigned default database.

The Error Message Pain Scale Varies

The pain scale for situations such as this depends on how you're hitting the SQL Server database. Are you attempting to connect without specifying the target database in your connection string and opting for relying upon the default database setting for the login involved? Perhaps the command being supplied is fully-qualifying the object being queried (which I always recommend) such as the following command:

SELECT columnXFROM database.schema.table;

What application or source are the users connecting from as their application source? In SQL Server Management Studio (not a connection source I'd recommend for end users due to security concerns) the error message may be quite explicit and get you or the user on the path for identifying the root cause quickly:

In Visual Studio a developer may encounter a more vague connection error:

Or a developer experiencing the same root cause while connecting via Python may have an even worse error handling/identification experience that may leave everyone's head spinning:

Regardless of the presented error, identifying the situation involving a dropped default database is solved with a simple addition to the query provided above.

The Script to Identify Dropped Default Databases and their Logins

-------------------------------------------/*DROPPING THE DATABASE DOESN'T DROP THE DEFAULTDATABASE ASSIGNMENT. NEED TO IDENTIFY IF THE DATABASE STILL EXISTS WITH A SIMPLE JOIN*/-------------------------------------------SELECT SP.name, SP.default_database_name FROM sys.server_principals AS SPLEFT JOIN sys.databases AS DON SP.default_database_name = D.nameWHERE D.name IS NULL AND SP.type_desc != 'SERVER_ROLE' ORDER BY SP.name;

Adding that LEFT JOIN to the source of truth for databases on a SQL Server instance allows us to identify logins where a default database has been assigned yet no longer exists on the instance. I sure hope I didn't need that database for anything important!

Remediation: Drop Login or Re-Assign Default Database

As I finish this article one item is left unanswered: the comment requested to also include a dynamic drop of the login for any of these hits. I want to honor that but at the same time not include that in the assessment script above because not all DBAs would want to drop the logins. The database may not be of use anymore but many SQL Server logins on an instance need access to multiple databases. Therefore I'm going to give you two options:

Script to dynamically drop the login with a missing default database.

Script to dynamically change the default database for the login(s) identified. 

Script to Dynamically Drop a Login with Missing Default Database

Drafting a script to provide a dynamic login drop is easy. Easy doesn't mean it's the right course of action as  you'll see next so please use caution when dropping logins just because you dropped their default database previously. The login could be associated with user objects in other databases.

--------------------------------------------/*DYNAMIC SQL TO SCRIPT LOGIN DROP*/--------------------------------------------SELECT SP.name, SP.default_database_name , 'DROP LOGIN [' + SP.name +'];'FROM sys.server_principals AS SPLEFT JOIN sys.databases AS DON SP.default_database_name = D.nameWHERE D.name IS NULL AND SP.type_desc != 'SERVER_ROLE' ORDER BY SP.name;

Script to Dynamically Change a Login's Default Database

Earlier on when I created the foofoofoo login I not only granted it rights to it's default database (foo) but also to two other databases I intended not to drop for this exercise so you can see what will happen if a login's default database is dropped yet it has rights in other databases. Certainly we'd not want to drop the login as the comment provider asked because we then disrupt the ability for the login to query other databases it should be able to query. What the script below does is identify any logins missing their default database and provide a dynamic script to set that to any one or more of the existing databases they've rights to through a matched login:user relationship.

---------------------------------------------------/*DYNAMICALLY SCRIPT NEW DEFAULT DATABASE ASSIGNMENTFOR ANY LOGIN MISSING THEIR DEFAULT DATABASE*/---------------------------------------------------SET NOCOUNT ON;DECLARE @sql_text nvarchar(max);DECLARE @database_name sysname;IF EXISTS(SELECT name FROM tempdb.sys.tables WHERE name LIKE '#existing_db_users_table%')BEGINDROP TABLE #existing_db_users_table;ENDCREATE TABLE #existing_db_users_table (server_login_name sysname NOT NULL, database_user_name sysname NOT NULL, default_database_name sysname NOT NULL);DECLARE cur_default_databases CURSOR FORWARD_ONLY STATIC FORSELECT name FROM sys.databases ORDER BY nameOPEN cur_default_databasesFETCH NEXT FROM cur_default_databases INTO @database_nameWHILE @@FETCH_STATUS = 0BEGINSELECT @sql_text = 'INSERT INTO #existing_db_users_table(server_login_name, database_user_name, default_database_name)SELECT SP.name AS [server_login_name], DP.name AS [database_user_name], ' + '''' + @database_name + '''' + ' FROM sys.server_principals AS SPINNER JOIN ' + @database_name + '.sys.database_principals AS DPON SP.sid = DP.sidWHERE SP.default_database_name != ''' + @database_name + ''';'EXEC sys.sp_sqlexec @sql_text;FETCH NEXT FROM cur_default_databases INTO @database_nameEND CLOSE cur_default_databases;DEALLOCATE cur_default_databases;--===================================================-- LISTING OF POTENTIAL DEFAULT DATABASE ASSIGNMENTS--===================================================SELECT SP.name AS login_name, SP.default_database_name, DDUT.default_database_name AS potential_default_database_name, CASE WHEN DDUT.default_database_name IS NULL THEN 'Login has no other DB user associations.'ELSE 'ALTER LOGIN [' + SP.name + '] WITH DEFAULT_DATABASE=[' + DDUT.default_database_name + '];'END AS potential_default_database_commandFROM sys.server_principals AS SPLEFT JOIN #existing_db_users_table AS DDUTON SP.name = DDUT.server_login_nameLEFT JOIN sys.databases AS DON SP.default_database_name = D.nameWHERE SP.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN')AND SP.name NOT LIKE 'NT %'AND SP.name NOT LIKE '##%'AND SP.default_database_name IS NOT NULL--AND DDUT.server_login_name IS NOT NULLAND D.name IS NULLORDER BY SP.name, DDUT.default_database_name;--===================================================-- CLEANUP--===================================================IF EXISTS(SELECT name FROM tempdb.sys.tables WHERE name LIKE '#missing_existing_db_users_table%')BEGINDROP TABLE #missing_existing_db_users_table;ENDGO

If there are multiple hits it's up to the discretion of the DBA to execute the ALTER LOGIN command that makes the most sense for the login. That's yet another reason why I assert being a good DBA is not just about knowing the inner workings of the data platform being supported but also understanding the business rules and use patterns of the databases being supported.

Conclusion

Dropping databases should definitely be handled with care. The consequences can be disastrous. However this article should serve as a tool to help you recover from situations where the drop of the database was warranted but unintended results occurred because of security dependencies on the database that was removed. I'm always looking for suggestions on topics to write about and this article came as a result of a reader request. Developing topics for articles is the hardest part of the writing process so if you've something you'd like explained in the style in which I write please contact me or comment on articles written here.