Fix: The server principal “<username>” is not able to access the database “<SQL_DB>” under the current security context.

Scenario:

When you copy a SQL DB (detach from the source server and attach on the destination server) between SQL servers running under same instances, you’ll see the SQL user login on the attached server fails with below error:

The server principal “<username>” is not able to access the database “<SQL_DB>” under the current security context.

Any application that attempting to connect to the DB will report the Login error as shown below:

 

—————————
Login failed. Check the log file for details.
—————————
Login Error
—————————
OK  
—————————

 

Additionally when you try to connect to Database via ODBC manager, it as well reports the login error as shown below:

—————————

Microsoft SQL Server Login

—————————

Connection failed:

SQLState: ‘28000’

SQL Server Error: 18456

[Microsoft][SQL Native Client][SQL Server]Login failed for user ‘AssetUser’.

—————————

OK

—————————

 

 

Cause:

Such an issue happens due to the login user account mismatch on the server where the DB is now attached. If you already have the application installed on the new DB server instance, it probably might create the appropriate user accounts at the instance level for the user. However, when you attach the copied DB, it will have the user account attached that is created on the server where it’s detached from. Thus these two accounts SID’s doesn’t match causing the SQL server to deny the login to the DB.

Below is the SQL DB level verification that account having different SIDs at instance and DB levels.

Text:

sp_change_users_login @Action=’Report’;

SELECT sid FROM sys.sysusers WHERE name = ‘AssetUser’

SELECT sid FROM sys.syslogins WHERE name = ‘AssetUser’

EXEC sp_change_users_login @Action=’update_one’, @UserNamePattern=’AssetUser’,@LoginName=’AssetUser’;

 

UserName UserSID

AssetReader 0xF3CDAC0C3350E2408B44E3437CCB0D15

AssetUser 0x71618DC33893074FAD9BE37D909A0F94

sid

0x71618DC33893074FAD9BE37D909A0F94

sid

0xA93572199036F141B4882B3A78CEDBEE

 

MSFT explanation:

After you transfer logins and passwords to the destination server, users may be unable to access the database. Logins are associated to users by the security identifier (SID), and if the SID is inconsistent after you move a database, SQL Server may deny the user access to the database. This problem is known as an orphaned user. If you transfer logins and passwords by using the SQL Server 2000 DTS Transfer Login feature, you will probably have orphaned users. Additionally, integrated logins granted access on a destination server in a different domain than the source server cause orphaned users.

 

FIX:

The fix is to resolve the differing SIDs for the same named user accounts. You can run below command to do that:

 

Verification:

 

References:
 How to move databases between computers that are running SQL Server

Troubleshooting Orphaned Users

The server principal “XYuser” is not able to access the database “Ydb” under the current security context