How to associate in Sql Server a database user with an existing Sql Server login

How to associate in Sql Server a database user with an existing Sql Server login

  
Hi

From time to time (since Sql Server 7), I bump on this problem. Sometimes, when copying, restoring or attaching a database, sql server does not assign a login with a database user.

Today, I experienced this behavior when copying a database from one sql server in one machine to other sql server in another machine. Both are Sql Server 2005 SP2. I used MS SQL Server Management Studio to this purpose: on the tree <server>\<sourcedb>, right clicked and selected Tasks\Copy Database...

OutSystems Platform (Express 4.1) failed the installation with the error:
Hub Server Configuration Tool failed with error code 19


I'd say the problem is related with the internal identifiers of Sql Server logins being different, although the name is the same.

To check that this is the problem, use MS SQL Server Management Studio to see the properties of the database user: on the tree <server>\<destinationdb>\Security\Users\<user>, right click and select Properties. The login related info is not editable, If 'Without login' is selected, you must fix it.

To fix it, open a new query and execute the following:

use outsystems_express -- use your database name
EXEC sp_change_users_login 'Update_One', 'OSAdmin', 'OSAdmin'
EXEC sp_change_users_login 'Update_One', 'OSRuntime', 'OSRuntime'
EXEC sp_change_users_login 'Update_One', 'OSLog', 'OSLog'

Regards,
António Melo