During an SQL database migration (from an physical to an virtual SQL server) I ran into the following problem.
I first created a SQL backup from the application database , let’s call the database app1_db, on the physical SQL (2005) server. On the next step I’ve created a new database
user (app1_dbuser) for this database on the virtual SQL (2005) server.
Next up I’ve restored the database on the virtual SQL server. When I tried to map the newly created user to app1_db, the database produced the following SQL error:
User, group or role ‘app1_dbuser’ already exists in the current database (Microsoft SQL Server, Error 15023)
This problem occurs because you only restore the application database, and not the master database where logins are kept. You expect a restored database to be in exactly the same state as the backup, but the login fails for a user that had permissions in the backed up database. This is caused by Security Identification numbers (SID) that are mismatched or ‘orphaned’ in the SysUser table.
To fix this problem you have to execute the SQL Server built-in stored procedure within SQL Management Studio.
EXEC SP_CHANGE_USERS_LOGIN ‘AUTO_FIX’, ‘app1_dbuser’
In the output screen you will see three lines that indicate that the auto fix was successful.
Was once an enthusiastic PepperByte employee but is now working at Ivanti. His blogs are still valuable to us and we hope to you too.