Fixing Orphaned Users in SQL Server Database

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'UserA')
DROP LOGIN [UserA]
GO

CREATE LOGIN [UserA] WITH PASSWORD=N'UserA',
         DEFAULT_DATABASE=[OrphanedUsers],
         CHECK_EXPIRATION=OFF,
         CHECK_POLICY=OFF
GO

/* Create SQL Server Login "UserB" on SQLServerB Instance */
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'UserB')
DROP LOGIN [UserB]
GO

CREATE LOGIN [UserB] WITH PASSWORD=N'UserB',
         DEFAULT_DATABASE=[OrphanedUsers],
         CHECK_EXPIRATION=OFF,
         CHECK_POLICY=OFF
GO

Now if you try to get connected to the SQLServerB server using either of the SQL Server Logins UserA or UserB which were created in the previous step we expect to get connected. However, you will see the below mentioned error when trying to get connected.

Cannot open user default database. Login failed.
Login failed for user 'UserA'. (Microsoft SQL Server, Error: 4064)

Cannot open user default database. Login failed.
Login failed for user 'UserB'. (Microsoft SQL Server, Error: 4064)

The reason for this failure is that the SID’s for UserA and UserB at the SQLServerB Instance is not matching with the SID’s in OrphanedUsers Database.

Verify User and SID value in SQLServerB Instance
Execute the below TSQL script to verify the SID’s in the system catalogs of SQLServerB Instance.

SELECT name, sid, default_database_name FROM master.sys.server_principals
WHERE name IN ('UserA','UserB')

SELECT name, sid, loginname, dbname FROM master.sys.syslogins
WHERE name IN ('UserA','UserB')

SELECT name, sid FROM OrphanedUsers.sys.database_principals
WHERE name IN ('UserA','UserB')

SELECT name, sid FROM OrphanedUsers.sys.sysusers
WHERE name IN ('UserA','UserB')



From the above snippet it is very clear that the SID value between sys.server_principals, sys.logins and sys.database_principals, sys.sysusers are different. As a result you are not able to get connected to the OrphanedUsers database using either of the SQL Server Logins UserA or UserB. Thus both the users have become orphaned users. An Orphaned User in SQL Server is a database user for which a valid SQL Server Login is not available or it is wrongly defined on the instance of SQL Server, thereby not allowing the user to get connected to the database.