<

[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x

SQL Server: "Error '15023' User or role already exists in the current database" when you perform restore database from backup

Published on
17,039 Points
10,839 Views
2 Endorsements
Last Modified:
Approved
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to allow necessary user permissions to the new database. This is puzzling enough since a database backup should bring us back to the original state. This is caused by Security Identification numbers (SID) that are mismatched or 'orphaned' in the sysusers table.

To resolve this problem, we can make use of the SQL Server stored procedure sp_change_users_login.

From MSDN, the syntax of sp_change_users_login is:

sp_change_users_login [ @Action= ] 'action'
    [ , [ @UserNamePattern= ] 'user' ]
    [ , [ @LoginName= ] 'login' ]
    [ , [ @Password= ] 'password' ]
[;]

1. Switch to the target database


use mydatabasename

Open in new window


2. List problematic SID


Lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login. user, login, and password must be NULL or not specified. This step is informative and thus optional.

exec sp_change_users_login @Action='Report'

Open in new window


3. Fix problematic SID


Links the specified user in the current database to an existing SQL Server login. User and login must be specified. password must be NULL or not specified. The login name(s) to provide is/are from the result reported in step 2.

exec sp_change_users_login @Action='Update_One', @UserNamePattern='MyLoginID', @LoginName='MyLoginID'

Open in new window


Repeat running 'Update_One' statement for all login names.

4. Verify all is okay now


Repeat the sql statement with Action='Report' as described in step 2 to confirm we have resolved all login names. If no records are returned after running Action='Report', we are 99% there!

Now, try logging in using the "just-fixed" SQL user login id to confirm we have resolved "Error '15023'".

TIP: You may wish to save the your sql statements into a .sql file so you can reuse it in future database migration/restore.


MSDN Reference: http://msdn.microsoft.com/en-us/library/ms174378.aspx


To view the original article and discussion you can visit my blog here.


Cheers
hongjun
2
Comment
Author:hongjun
1 Comment
 
LVL 7

Expert Comment

by:Yashwant Vishwakarma
Very helpful article
Voted as Good Article :)
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Join & Write a Comment

Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month