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"

 

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.)

 

  1. Take for example the situation described by the comment above: create a login and assign a default database.
  2. Add a user to the default database to mimic what would have to be in place for successful connectivity to the SQL Server instance.
  3. Drop the default database

 

                              
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=OFF
GO

USE [foo]
GO
CREATE 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]
GO
CREATE USER [foofoofoo] FOR LOGIN [foofoofoo] WITH DEFAULT_SCHEMA=[dbo]
GO

USE [iDBA]
GO
CREATE 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 columnX
FROM 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 DEFAULT
DATABASE 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 SP
        LEFT JOIN sys.databases AS D
                ON SP.default_database_name = D.name
WHERE 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 SP
        LEFT JOIN sys.databases AS D
                ON SP.default_database_name = D.name
WHERE 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 ASSIGNMENT
FOR 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%')
        BEGIN
                DROP TABLE #existing_db_users_table;
        END

CREATE 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 FOR
        SELECT name
        FROM sys.databases
        ORDER BY name

OPEN cur_default_databases

FETCH NEXT FROM cur_default_databases INTO @database_name

WHILE @@FETCH_STATUS = 0
        BEGIN

                SELECT @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 SP
                        INNER JOIN ' + @database_name + '.sys.database_principals AS DP
                                ON SP.sid = DP.sid
                WHERE SP.default_database_name != ''' + @database_name + ''';'

                EXEC sys.sp_sqlexec @sql_text;

                FETCH NEXT FROM cur_default_databases INTO @database_name
        END

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_command
FROM sys.server_principals AS SP
        LEFT JOIN #existing_db_users_table AS DDUT
                ON SP.name = DDUT.server_login_name
        LEFT JOIN sys.databases AS D
                ON SP.default_database_name = D.name
WHERE 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 NULL
        AND D.name IS NULL
ORDER BY SP.name, DDUT.default_database_name;


--===================================================
-- CLEANUP
--===================================================
IF EXISTS(SELECT name FROM tempdb.sys.tables WHERE name LIKE '#missing_existing_db_users_table%')
        BEGIN
                DROP TABLE #missing_existing_db_users_table;
        END
GO

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.