I've written a few articles lately that revisit the concept of "orphaned users": Users that exist in SQL Server databases but lack an associated login which prevents connectivity for (potential) consumers of data in these instances. The original article explaining orphaned users can be found here. Since then I've looked at the wider concept of what new SQL Server Database Administrators should understand about security at a base level and circled-back to orphaned logins from a different perspective: situations where a login exists but has since had its default database dropped preventing connectivity from the other side of the coin.
This article looks into one other issue that arose for me recently with a client. They had migrated their SQL Server instances from SQL Server 2012 to SQL Server 2016 yet didn't do a complete job of migrating over their logins causing connectivity issues because some of the logins were missing a corresponding user in their default database. We now look at (a) how that can happen and (b) how to identify and correct it.
How Could this Happen?
The root cause of the issue described here is the existence of a login object on a SQL Server instance with an assigned default database, but with no user object associated with the login existing in the default database. This can happen in a variety of ways:
- Someone has created a login with a default database but never followed up by creating a user in the database that is associated with the login.
- You or another DBA has migrated logins and databases from a different instance or consolidated from multiple instances and while the login was created the default database doesn't include a user for the login. Typically this would result in one of two things:
- No default database that matches the one assigned to the login - the assigned default database was never migrated. This would present itself as an issue I addressed in this article.
- Either a copy of a database with the same name as the login's default database was migrated or created from scratch.
- The user was dropped from the database without dropping the associated login.
Login creation does not automatically trigger the creation of any database users. These are two separate events.
Creating a login establishes a instance-level entity that is either associated with an Active Directory object (Windows/Trusted Authentication) or is associated with a combination of login name and stored password (SQL Server Authentication):
CREATE LOGIN [timford]
WITH PASSWORD=N'thisissomepasswordisntit?'
, DEFAULT_DATABASE=[SQL_Cruise]
, CHECK_EXPIRATION=OFF
, CHECK_POLICY=OFF;
The process of creating a user establishes a database-scoped object that is linked back to an instance-level login based upon a sid value (unique identifier of a security object instance-wide in Microsoft SQL Server):
USE [SQL_Cruise]
GO
CREATE USER [timford] FOR LOGIN [timford];
Migrating a login from one SQL Server to another only creates the login on the instance - it does not provide for either migrating the database or any of the database objects. Depending on how you migrate the login it will also bring the settings for the default database with it from the previous instance without establishing whether the database exists on the new instance or if it does if it has user associated with the login. If the login, database, and user exists but the sid does not match then you end up the the classic orphaned user situation and you need to sync the sid values between login and user. If the login does not exist then you end up dealing with what we're discussing here.
Finally the case of the dropped user. When dropping a user from a database the login will not be dropped as well. This is critical as you may have a login with user rights in multiple databases on an instance.
In the case I encountered all the issues we related to logins with their default database set to be the master system database. This happens frequently since it's a guarantee that the master database exists on an instance of Microsoft SQL Server - you'll never end up in a situation where you drop the master database. However you're not going to migrate your master database when you migrate SQL Server instances to newer versions of SQL Server. Likely the only time you'll migrate the master database is when you find yourself in either a hardware replacement situation and you're migrating an entire instance to new hardware as a planned action - or you've suffered a server-wide failure where your only option is restoring all of your databases from backup to new hardware. Since the master database was created from scratch when SQL Server 2016 was installed none of the users that existed in the SQL 2012 instance's master database were migrated. The end result was that every login with master as a default database had a missing link in the authentication process for the login. In my case the identification and resolution was easy: identify all logins with master as their default database and create a user for each in the master database.
The Solution was not as Simple as it Seemed
I stated earlier that this isn't always going to manifest with the master database being the root cause for all your connectivity woes. Consolidation is another fine example as is migrating between levels of development, testing, and production and therefore the solution below needs to work for any and all databases on a SQL Server instance. At first I figured this would be a simple script because the logical approach to defining the problem is a simple one:
Check the default database assigned to each login and verify there is a user with a matching sid value for the login.
This is easier said than done since you need to execute a query in each database for each login and save off the results centrally for any situation where there is no matching user. I opted for a construct that gets a lot of bad press from many thought leaders in the Microsoft Data Platform community: cursors. When used appropriately though cursors can solve a lot of issues without creating the performance problems their known to cause. Therefore for this solution I opted to employ a cursor to step through each database and collect a listing of all results for the combination of login, user, and default database based upon the value of any sid matches in the two system views that contain the metadata for server logins (master.sys.server_principals) and database users (sys._database_principals) which exists in every database. The results are stored to a temporary table through use of the sys.sp_sqlexec stored procedure that is used to execute the ad-hoc dynamic SQL created through the cursor in the context of each database. Finally those results are compared against the full master.sys.server_principals view for any missing users and reported as the results of the query.
--============================================================
--IDENTIFY ALL LOGINS WITH NO ASSOCIATED DEFAULT DATABASE USER
--============================================================
SET NOCOUNT ON;
DECLARE @sql_text nvarchar(max);
DECLARE @database_name sysname;
IF EXISTS(SELECT name FROM tempdb.sys.tables WHERE name LIKE '#default_db_users_table%')
BEGIN
DROP TABLE #default_db_users_table;
END
CREATE TABLE #default_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 --FORWARD_ONLY STATIC CURSOR 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 #default_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], SP.default_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 LOGINS MISSING USER IN DEFAULT DATABASE
--===================================================
SELECT SP.name AS login_name
, SP.default_database_name
, 'USE [' + SP.default_database_name + ']; CREATE USER [' + SP.name + '] FOR LOGIN [' + SP.name + '];' AS user_create_stmt
FROM sys.server_principals AS SP
LEFT JOIN #default_db_users_table AS DDUT
ON SP.name = DDUT.server_login_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 NULL
ORDER BY SP.name;
--===================================================
-- CLEANUP
--===================================================
IF EXISTS(SELECT name FROM tempdb.sys.tables WHERE name LIKE '#missing_default_db_users_table%')
BEGIN
DROP TABLE #missing_default_db_users_table;
END
The last column also includes the dynamically-generated t-sql that can be used to create the user object in the assigned default database.
What Every Accidental DBA Needs to Know About the Basics of SQL Server Security
Conclusion
Understanding there is a multi-level relationship between logins, default databases, and users goes a long way towards triaging connectivity issue when one or more of those entities or assignments are missing. Hopefully after reviewing these articles and bookmarking them for later reference you'll have a few more scripts in your DBA toolkit for solving connectivity concerns quickly.
