BizTalkUser is not able to access the database DBName

In this post we will discuss about BizTalkUser issue that we found.

Microsoft.ServiceModel.Channels.Common.ConnectionException: The server principal “BizTalkUser” is not able to access the database “<DBName>” under the current security context.
Cannot open database “<DBName>” requested by the login. The login failed.
Login failed for user ‘BizTalkUser‘. —> System.Data.SqlClient.SqlException: The server principal “BizTalkUser” is not able to access the database “<DBName>” under the current security context.
Cannot open database “<DBName>” requested by the login. The login failed.
Login failed for user ‘BizTalkUser‘.
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
   at System.Data.SqlClient.SqlConnectio

Cause: When you restore a backup database to another server, you may experience a problem with orphaned users. Or if you missed to provide the proper access to user on database server.

Solution1: 

To detect orphaned users you can run the following command:

USE <DBName>sp_change_users_login  @Action=’Report’;

You can see that the SID does not match the system views: sys.sysusers and sys.syslogins

USE <DBName>SELECT sid FROM sys.sysusers WHERE name = ‘sqlLoginName’SELECT sid FROM sys.syslogins WHERE name = ‘sqlLoginName’

To correct this problem of connection between the server connection account specified by the user and <login_name> the database specified by <database_user>, you can run the following command.

USE <DBName>EXEC  sp_change_users_login @Action=’update_one’, @UserNamePattern=’sqlLoginName’,@LoginName=’ sqlLoginName ‘; 

Solution2: 

You can run the following command and check if user have proper access in sys.database_principals :

SELECT DB_NAME() as DBName, NAME as UserName, ‘CREATE USER [‘ + NAME + ‘] FOR LOGIN [‘ + NAME + ‘]’ AS qryFROM sys.database_principalsWHERE Type IN ( ‘U’ ,’S’ ) AND NAME NOT IN ( ‘dbo’ ,’guest’ ,’sys’ ,’INFORMATION_SCHEMA’ )Union allSELECT DB_NAME() as DBName,NAME as UserName, ‘CREATE USER [‘ + NAME + ‘] FROM EXTERNAL PROVIDER’ AS  qryFROM sys.database_principalsWHERE Type IN ( ‘E’ ) AND NAME NOT IN ( ‘dbo’ ,’guest’ ,’sys’ ,’INFORMATION_SCHEMA’ ) 

If user is not listed in above list then please add this user in sys.database_principals with following command. The second step is to generate the database roles membership for each user as EXEC sp_AddRoleMember statement:

SELECT ‘EXEC sp_AddRoleMember ”’ + DBRole.NAME + ”’, ”’ + DBUser.NAME + ”” AS ‘–Add Users to Database Roles–‘FROM sys.database_principals DBUserINNER JOIN sys.database_role_members DBM ON DBM.member_principal_id = DBUser.principal_idINNER JOIN sys.database_principals DBRole ON DBRole.principal_id = DBM.role_principal_id

Now you have the scripts to create each user on the database level, map it to its corresponding login and grant it access by adding it to the database roles. The last step is to apply this script to your database after the restoration process.