Sync the SQL user ID

After moving the database from one SQL server to another, the user ID (that has been created in the database) is no longer working. This is mainly due to the "SQL login ID" does not match with the "database user ID". The value that links between these ID-s are call the SID.


Before executing the fixes, you may wonder what has happened to the user ID. Execute the following query:

   use [my_database]
   go
   select * from sysusers where name = 'my_user_name'
   select * from master..syslogins where loginname = 'my_login_name'
   go


From the result, you might notice that the "sid" value in both queries are different. And this is the cause of the login problem.


In MSSQL, there is a system stored procedure that will help you to sync the SID value and it is called "sp_change_users_login".

To get the list of the mismatch user ID, execute this command.

    exec sp_change_users_login 'report'

To sync the database user ID with the SQL login ID:

   exec sp_change_users_login 'update_one', 'my_user_name', 'my_login_name'

Note: you have to replace "my_user_name" and "my_login_name" with the actual values.