The next step after the successful database restore will be to logon to SQLServerB Instance using either of the SQL Server Logins namely UserA or UserB. However, you could see that on SQLServerB you will not be able to get connected as both the logins are not present and the attempt to login will fail with the below mentioned error.
Login failed for user ‘UserA’. (Microsoft SQL Server, Error: 18456)
Login failed for user ‘UserB’. (Microsoft SQL Server, Error: 18456)
Lets us now resolve the SQL Server Login issue by adding UserA & UserB logins on SQLServerB Instance by executing the below mentioned TSQL scripts.
/* Create SQL Server Login “UserA” on SQLServerB Instance */
USE [master]
GO
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.