Overview

On several occasions I have inherited SQL Server instances that I did not setup but that I will inevitably become responsible for supporting. In the process of taking inventory of the instance I have often times encountered orphaned Database Users. An orphaned Database Users was related to a SQL Server login at one time but is no longer. If you’re anything like me then your initial reaction when seeing something like this is to explore the possibility of dropping orphaned Database Users. Except, how can we differentiate between a Database User who had their Server Login dropped and a Database User that never had a Server Login to begin with?

Background

It is important to understand the distinction between a Server Login and a Database User. A Server Login authenticates a person to an Instance via either Windows Authentication per the person’s Windows Identity, or in the case of SQL Server Authentication a Login Name and Password. A Database User is used to authorize a Server Login to access a specific objects and data within a database.

To reiterate the point in case it was unclear, Server Logins and Database Users are two different security objects within an Instance that play different roles. Server Logins reside at the Instance-level and Database Users reside at the Database-level. They work together to ensure people can access only the Instance and Database resources and functions they are permitted to access. Logins and Users work hand-in-hand and if we inspect the syntax used to create a new Database User we quickly notice that a Server Login can be referenced:

CREATE USER [John.Doe] FROM LOGIN [DOMAIN\John.Doe];

A typical sequence of events that leads to a Database User becoming orphaned:

1. A new employee is hired by the organization.
2. A request is generated to have a DBA add a new Server Login and several Database Users to a production database instance so the new employee can interact with various databases required to do their job.
3. Some time goes by and the employee leaves the organization.
4. A request is generated for a DBA to remove the former employee’s access from all database instances.
5. The DBA tasked with removing database access, either because they are lazy or because they are not fully versed in administrating SQL Server security, simply drops the Server Logins from the instance thereby creating several orphaned Database Users.

Now the wrinkle. Not all legitimate Database Users are linked to Server Logins. A Database User can be created without a Login. A quick look at the syntax to create a loginless Database User shows us quite an intuitive statement:

CREATE USER [Loginless.User] WITHOUT LOGIN;

A Database User created without a Login can offer special utility within a database as it relates to exposing functionality through stored procedures for low-privileged users to execute while not having to grant the calling user the ability to use certain functionality directly. A ready example is when you want to allow a user to execute a stored procedure that executes dynamic SQL but you do not want to grant that user access to the tables accessed by the dynamic SQL.

Analysis

When looking into which Database Users can safely be dropped from a database we must differentiate between Users that were created from a Login and Loginless Users. Below is a query that can identify which Database Users do not have an associated Server Login and were created from a Server Login:

-- report orphaned users, ignoring loginless users, in all databases
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';
SELECT  @sql = @sql + N'UNION ALL
SELECT  ''' + name + N''' AS [db_name],
        dp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS db_user,
        dp.type_desc AS user_type,
        CASE WHEN EXISTS ( SELECT   *
                           FROM     ' + QUOTENAME(name) + N'.sys.sql_modules
                           WHERE    definition LIKE (N''%'' + dp.name + N''%'') COLLATE SQL_Latin1_General_CP1_CI_AS) THEN 1
             ELSE 0
        END AS db_user_is_referenced_in_code
FROM    ' + QUOTENAME(name) + N'.sys.database_principals dp
        LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE   -- database user does not have an associated server login
        sp.sid IS NULL
        AND dp.name NOT IN (N''guest'', N''dbo'')
        AND (
             dp.type_desc = N''WINDOWS_USER''
             OR (
                 -- what differentiates a user created without login versus
                 -- one created from a login and later orphaned is the
                 -- length of the sid coupled with the database-user-type
                 dp.type_desc = N''SQL_USER''
                 AND LEN(dp.sid) <= 16
                )
            )
'
FROM    sys.databases
WHERE   name NOT IN (N'tempdb')
        AND state_desc = N'ONLINE';
-- remove leading UNION ALL from the generated SQL
SET @sql = STUFF(@sql, 1, 9, '');
-- prepend the necessary SQL to put the results into a temporary table
SET @sql = N'SELECT * INTO ##tmp FROM (' + @sql + N') s;';
-- delete the temporary table if it exists
IF OBJECT_ID(N'tempdb..##tmp') IS NOT NULL
    DROP TABLE ##tmp;
-- execute the SQL to build and populate the temp table
EXEC(@sql);
-- see the results
SELECT  *
FROM    ##tmp;

Query 1. Show orphaned Database Users, but only ones that were created from a Server Login

The results of Query1 above will show us all Database Users in the Instance that were originally linked to a Server Login but are now orphaned. Keep in mind that orphaned Users might still be referenced in EXECUTE AS statements so pay attention to the db_user_is_referenced_in_code column. If the value of db_user_is_referenced_in_code is 1, then further impact analysis is required before dropping the orphaned user, and a thorough impact analysis should always be done prior to dropping any Database Users.

Why does the query work? How can we know for sure which Database Users were created from a Login and which ones were created without a Login? The table below shows the factors and the combination of them make the choice deterministic: 

Table 1: properties of the various types of Database Users

SELECT  name,
        type_desc,
        [sid],
        LEN([sid]) AS sid_len
FROM    sys.server_principals
WHERE   type_desc IN ('WINDOWS_LOGIN', 'SQL_LOGIN');

Query 2: view the SID for each Server Login in the sys.server_principals System View

SELECT  name,
        type_desc,
        [sid],
        LEN(sid) AS sid_len
FROM    sys.database_principals
WHERE   type_desc IN ('WINDOWS_USER', 'SQL_USER');

Query 3: view the SID for each Database User in the sys.database_principals Catalog View

Closing Notes

The queries in this article were tested on SQL Server 2005, SQL Server 2008 R2 and SQL Server 2012.

Before dropping any Database Users from a database it is critical that you do a complete impact analysis to ensure there will be no ill effects.

Please join the article discussion and let us know what you uncover in your environment. If you found orphaned Users you thought you could drop please share how you went about performing impact analysis. 

References

• CREATE LOGIN
• CREATE USER
• Source code for sp_change_users_login