ardalis

Steve Smith, software craftsman

Connect with me:

  • Blog
  • Training
  • Mentoring
  • Dev Tips
  • Architecture eBook
  • Tools Used
  • About

ardalis 19 Jun 2010 42 Comments

SQL Server Error User Group or Role Already Exists in the Current Database

If you restore a database and then try to login to it, you’re likely to run into this wonderful SQL Error:

User, group, or role ‘whatever’ already exists in the current database (Microsoft SQL Server, Error: 15023).

Unfortunately, using Sql Management Studio alone doesn’t seem up to the task of correcting this problem.  You have to drop down to calling esoteric stored procedures (who needs a GUI to actually manage users and logins, right?).

Searching for this error at least yields many results like these.  I especially like the second one whose title ends with ‘Aarrgghh!!’ which led to me clicking it since it represented my current thoughts on the matter quite succinctly.

In short order, you will learn about the need to call “sp_change_users_login” to correct this problem, which is known as the ‘orphan user’ problem.  Of course, the results above don’t actually show you the syntax required, so you will have to run another search for that sproc name which will lead you to the MSDN documentation for sp_change_users_login (Transact-SQL).

Let me save you some time.  If you have a user in your recently restored database named ‘someuser’ and you have already created the login on the server (which is why you got the …already exists in the current database… error), then all you have to run is this:

Fix Login User
Transact-SQL
1
sp_change_users_login 'AUTO_FIX', 'someuser'

 
You should see results similar to this:

The row for user ‘someuser’ will be fixed by updating its login link to a login already in existence.

The number of orphaned users fixed by updating users was 1.

The number of orphaned users fixed by adding new logins and then updating users was 0.

Hope that saves you some frustration.

Filed Under: Uncategorized

About ardalis

Steve is an experienced software architect and trainer focused on improving team skills with DDD and ASP.NET Core. His courses on Pluralsight and DevIQ help developers write better, more maintainable code. He is available for application assessments and team mentoring engagements.

Sign up to receive a free developer tip from Steve in your inbox every Wednesday.

  • The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON

    In addition to the dreaded SQL Server Error: User Group or Role Already Exists in the Current Database error, you may also get this error when creating new logins after a database move: Alter failed for login ‘somelogin’. An exception occurred while executing

  • Chuck

    I am glad I stumbled upon this. This helped me out greatly. I was stumped when I brought a production DB down to my local setup.

  • gw

    O..M..G.. been down the google highway for 3 hrs doing obscure things like unchecking the "Collation" column (how’s THAT supposed to help?) and your post finally fixed my database. Thanks!

  • Tim

    Thank you for the post – been trying to work my way around this for the last hour or so and this worked great.

  • Paul

    Saved me a TON of grief as this was at the top of my google search. Thanks!!

  • Ardhajilla

    Thanks, this solved my issue very quick, Thanks Again!

  • Mask7861

    Wonderful!! Thanks a lot…saved huge time in finding the right solution.. 🙂

  • Talln6e

    OMG, thank you!  Note that this statement needs to run in the database you want to connect the user and login to.

  • Nitin4dba

    Perfectly fixed my login…. awesome !

  • Kevin

    Nice writeup – you saved me some legwork.

  • Rich K

    FANTASTIC – Thanks very much

  • Selva

    Thank you so much!

  • GaganT

    Thank you V m. It worked for me as well..

  • Gabe

    Thank you for this

  • MichiganGuitar

    You rock!

  • ssmith

    This thing still helps me from time to time…

  • Deepan

    Thank you!this saved
    me lot of time…

  • Fuse

    I can’t find that stored procedure anywhere. Where exactly is sp_change_users_login?

  • ssmith

    It’s usually in the master database:

    [master].[sys].[sp_change_users_login]

  • joe

    awesome. why can’t all explanations to simple problems also be simple like this.

    smart dba’s, too cynical and too stupid to explain simply.

    for 2008 and up use ALTER USER user WITH LOGIN = serverlogin

  • Anatoly Leonov

    A lot of thanks for you! Exactly what I needed!

  • Small

    Thanks for this, it saved a lot of time

  • Miranda Black

    Thank you very much, this really helped me. It was so kind of you to share it with everyone

  • tdbailey100

    Thanks! This saved me a huge headache today!

  • James Wachira

    Works like magic. Thanks alot

  • aelfwald

    Another grateful customer. Thank you!

  • Egbert

    Thanks a lot! Saved me a lot of frustration and lost hours

  • Raj

    Amazing. Worked great, Thanks!.

    ** Run on the relevant Database.

  • Bud Staniek

    You are the man. Thanx loads…

  • FERNANDO PENALOZA

    Half-day suffering, but solved my problem with your help 🙂 thank you very very much!

  • Andrej Podznoev

    Ohh man! It’s still relevant an saves lifes, thank you!

  • Osman Karimdere

    Thank you so much! It worked very well.

  • JS

    Saved me hours of searching! Bueno!

  • G F

    Thanks Steve!

  • Bhavna

    Thank you very much

  • Jane Prusakova

    Thank you, very helpful.

  • Bob Criswell

    Wow – This really helped me and did save me from some frustration. Thanks!

  • Daniel Foster

    I ran this, but got no results:
    The number of orphaned users fixed by updating users was 0.
    The number of orphaned users fixed by adding new logins and then updating users was 0.

    I was copying the database using the Copy Database wizard in order to create a test database on the same server, and got this error. The problem was that the source database had a user login mapped to that database. Somewhere in the mix of it all, the Copy Database wizard was trying to add a user to the destination database via a straight copy, but also add the same user through the mapping. The trick was to remove the mapping of the source database, then copy the database, then add the mapping back to the source (it was already added at the destination). Hope this helps for anyone else!

  • Bryan

    It worked. Thank you very much!

  • Anthony Griggs

    You are the MAN!!

  • Apple

    you got me dude! thanks

  • André Luis Nesso

    Very good. Thank very much brother…

Online Training

  • ASP.NET Core Quick Start
  • Domain-Driven Design Fundamentals
  • Refactoring Fundamentals
  • Kanban Fundamentals
  • SOLID Principles of OO Design
  • Pair Programming
  • Pluralsight FREE Trial

About Me

Steve is an experienced software architect and trainer focused on improving team skills with DDD and ASP.NET Core. His courses on Pluralsight and DevIQ help developers write better, more maintainable code. He is available for application assessments and team mentoring engagements.

Sign up to receive a free developer tip from Steve in your inbox every Wednesday.

Free Architecture eBook

Architecting Modern Web Applications with ASP.NET Core and Microsoft Azure Download PDF

Recent Articles

  • Momentum Dev Conference and Design Patterns
  • WeeklyDevTips Podcast Checklist
  • Positive Reinforcement in Code Reviews
  • Finding Things in Visual Studio 2017
  • How to Modify Visual Studio 2017 or Install Preview Versions

Popular Articles

  • Force Nuget to Reinstall Packages without Updating 1,083 views
  • How to add a Nuget Package Using dotnet add 768 views
  • How to find all objects in a SQL Server Schema 639 views
  • Architecture eBook 629 views
  • Why Delete Old Git Branches? 519 views

Recent Tweets by @ardalis

Tweets by @ardalis

Popular Articles

  • Architecture eBook 525 views
  • How to add a Nuget Package Using dotnet add 225 views
  • Force Nuget to Reinstall Packages without Updating 174 views
  • Using MediatR in ASPNET Core Apps 171 views
  • How to find all objects in a SQL Server Schema 106 views

More Resources

  • Contact Us
  • Interviews
  • Assessments
  • Mentoring
  • Training
  • Dev Tips Weekly
  • Archive

Dev Tips

Get a free developer tip in your inbox every Wednesday.

Become a better developer

Copyright © 2018 · Streamline Pro Theme on Genesis Framework · WordPress · Log in