• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Using sp_change_users_login to fix SQL Server orphaned users

September 19, 2012 by Andy Hayes 17 Comments

In this post, I’m going to be looking at sp_change_users_login in order to fix SQL Server orphaned users as a continuation to a previous article. There I looked at a couple of ways to transfer logins from one SQL Server to another and touched upon the issue of the orphaned “security identifier” (SID).

A typical scenario that arises is when the DBA quickly realises that the logins on the SQL Server cannot access the database. They try and add the login to the database as a user and are presented with the error:

Error 15023: User already exists in current database.

The root cause of this problem can be when a login is deleted or the database has been moved or restored to another server but the login transported in the database does not exist on the destination server.

Through SQL Server Management Studio, the database user properties will show the User type: as SQL user without login

sql user without login

sp_change_users_login to the rescue!

I first saw this error a number of years ago and due to my complete lack of experience at the time, one of my first thoughts was that I would have to remove the database users, re-add them all for each login requiring access and then proceed to add the permissions back in for user.

I quickly realised that this would be a massive waste of my time and that there had to a better way and so I proceeded to consult the search engines for a resolution. Unsurprisingly I quickly found many other people who had been in the same situation as me and that sp_change_users_login had been the cure to all their woes.

And here I am writing a post about it 🙂 Well I never would have imagined that but it was a long time ago and only clever people could put a website together back in those days when blogging platforms were a twinkle in some programmers eye.

How to use sp_change_users_login to fix SQL Server orphaned users

The best thing to do is run the following code against each database you are checking. Run this against each database. It will help you to find all the orphaned logins in your database.

[sourcecode language=’sql’]
USE DatabaseName
EXEC sp_change_users_login ‘Report’;[/sourcecode]

You will see output like the screenshot attached if there are any sql orphaned users. In this example, user “db_login1” is showing up as an orphaned user.

sp_change_users_login report

If you already have a login which you want to map your database user to, you could run the following (note that the first instance of ‘db_login1′ is the user in the database, the second instance is the login to be mapped to) :

[sourcecode language=’sql’]
EXEC sp_change_users_login ‘update_one’, ‘db_login1’, ‘db_login1’;
[/sourcecode]

If you don’t already have a login to map to, you can have sp_change_users_login create one for you and with a password. The following code does this and creates a login with the same name and a password of ‘aaZZww77′ as an example.

[sourcecode language=’sql’]
EXEC sp_change_users_login ‘Auto_Fix’, ‘db_login1’, NULL, ‘aaZZww77’;
[/sourcecode]

sp_change_users_login auto_fix

UPDATE – 3rd Apr 2018

An alternate way to detect orphaned users in SQL Server or PDW is by running this code for each database:

[sourcecode language=’sql’]

SELECT dp.type_desc, dp.SID, dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.SID = sp.SID
WHERE sp.SID IS NULL AND authentication_type_desc = ‘INSTANCE’;

[/sourcecode]

This is an example output:

detecting orphaned users in sql server using sp_change_users_login

For identifying orphaned users in Azure SQL Database and SQL Data Warehouse run this code in the master database:

[sourcecode language=’sql’]
SELECT sid FROM sys.sql_logins WHERE type = ‘S’;
[/sourcecode]

Now run this code in each database:

[sourcecode language=’sql’]
SELECT name, sid, principal_id
FROM sys.database_principals
WHERE type = ‘S’
AND name NOT IN (‘guest’, ‘INFORMATION_SCHEMA’, ‘sys’)
AND authentication_type_desc = ‘INSTANCE’;
[/sourcecode]

Compare the two lists to see if there are user SID’s in sys.database_principals which are not found in sys.sql_logins

Fix SQL Orphaned Users Using CREATE LOGIN

You can take the SID’s identified in the previous section and use them as part of the CREATE LOGIN statement, example:

[sourcecode language=’sql’]
CREATE LOGIN db_login_1
WITH PASSWORD = ‘use_a_strong_password_here’,
SID = 0xB171D3B5A352A846847342C5E46620BA;
[/sourcecode]

If you’re mapping an orphaned user to a login which already exists in master, run this:

[sourcecode language=’sql’]
ALTER USER WITH Login = ;
[/sourcecode]

For more info on how to fix orphaned users, click this sp_change_users_login link  and this one on troubleshooting orphaned users to view the documentation from Microsoft.

Filed Under: All Articles, SQL Server Administration, SQL Server Security Tagged With: security, sql server

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Comments

  1. Colin Mongardi says

    September 21, 2012 at 9:38 am

    Thanks, for this tip. (Out of ignorance) I have always gone for the solution of deleting/remapping the user. Which is fine if they just have simple permissions. How I miss the days of when I could just ask “someone” to sort out my database woes!

    Reply
    • Andy Hayes says

      September 21, 2012 at 5:14 pm

      Hi Colin

      I hope you are well and thanks for the comment.

      I’m glad you found this post useful 🙂

      All the best

      Andy

      Reply
  2. Dimple says

    January 15, 2013 at 11:00 am

    Thanks!

    Reply
  3. Shiny says

    April 27, 2013 at 8:41 pm

    Thanks so much for this 🙂

    Reply
  4. Shane Garven says

    June 28, 2013 at 7:44 am

    Thanks Andy. This has saved me today after some migrations by our DBA team from SQL 2005 to SQL 2008.
    Out of interest, what level of permissions are required to ensure the login is created, if it doesn’t exist in SQL?

    This worked for me. Although our cluster is locked down (as you would expect) and I was surprised it allowed me to create the login. Because I don’t think I could create it in the GUI-fashion. Server->Security->Logins->Right-Click->New Login.

    Thanks again.

    Reply
    • Andy Hayes says

      June 29, 2013 at 12:06 pm

      Hi Shane

      You need CREATE LOGIN permission to create logins in SQL Server. When you say that your cluster was locked down but you were able to create a login then the login which you authenticated to SQL Server with had/has CREATE LOGIN permission enabled.

      Thanks for your comment.

      Reply
  5. Pål says

    November 2, 2013 at 1:49 pm

    Thanks for a great article!

    Want to scan your databases for orphaned users?

    Try this:
    EXEC sp_MSforeachdb ‘USE ? EXEC sp_change_users_login ”report”’

    Reply
    • Andy Hayes says

      November 6, 2013 at 7:17 am

      Thank for the tip Pal, I’m glad you liked the article.

      Reply
  6. Taranjit Singh says

    June 16, 2014 at 4:58 am

    We can fix all the orphan users in the database by running through a small loop

    –Authored by Taranjit Singh
    –fix all orphan users

    –Get info of all orphan users into a temporary table
    –I am using update_one assuming login exist for all orphan users
    declare @userid varchar(255)
    Create Table #orphans
    ( UserName varchar(100),
    USID nvarchar(255)
    )
    insert into #orphans EXEC sp_change_users_login ‘report’

    –loop through all orphan users and fix those
    DECLARE Fixusers CURSOR FOR
    SELECT UserName
    FROM #orphans

    OPEN fixusers

    FETCH NEXT FROM fixusers
    INTO @userid

    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC sp_change_users_login ‘update_one’,@userid,@userid
    FETCH NEXT FROM fixusers
    INTO @userid
    END

    CLOSE fixusers
    DEALLOCATE fixusers

    DROP TABLE #orphans

    Reply
    • Andy Hayes says

      June 17, 2014 at 6:36 pm

      Thanks Taranjit!

      Reply
  7. Yvonne says

    January 9, 2015 at 6:47 pm

    Thank you for the tip, Andy! it just exact what I have encountered in my project of migrating SQL 2000 server to SQL 2012!

    Reply
    • Andy Hayes says

      January 11, 2015 at 1:09 pm

      Great! Glad this helped 🙂

      Reply
  8. Lakshmi says

    June 11, 2015 at 9:00 pm

    Hi ANdy,
    I have a question for you. I am migrating databases from 2008 to 2012. I generated the create script for all logins using the sp_help_rev_login code provided by Microsoft.Si I want to know if my thought process is ok.
    First run to see if there are any orphaned users.
    EXEC sp_MSforeachdb ‘USE ? EXEC sp_change_users_login ”report”’
    -I am expecting that there will be due to new SID creation.
    Then,
    1. If all the users be orphaned?
    if yes then I should run this SP_change_user_login for each login right? to make sure the logins are mapped to the users.

    Another question is I have multiple logins and running this for each will be tedious.
    Is the cursor code provided by taranjit the best way to go.
    PLease let me know your thought on this.

    Reply
  9. Yasser says

    August 2, 2016 at 7:53 am

    YOU ARE AWESOME !! your page’s link must be the first link showed by google.
    Thank you !

    Reply
    • Andy Hayes says

      August 7, 2016 at 6:58 pm

      Hey, I’m glad you like the post! 🙂

      Reply
  10. Gopinath says

    February 21, 2018 at 12:39 pm

    Hi Andy,

    When execute below query
    exec sp_change_users_login ‘Auto_Fix’,’domain\username’,NULL,’[email protected]′

    am getting below message
    ‘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.’

    Login is not created, May I know did I missed anything?

    Reply
  11. Manish says

    February 18, 2021 at 6:08 am

    /*fix orphan users in one particular database */

    declare @query varchar(1000)
    declare @executequery cursor
    set @executequery=cursor for
    select ‘ sp_change_users_login ‘+CHAR(39)+’update_one’+CHAR(39)
    +’,’+CHAR(39)+name+CHAR(39)+’,’+CHAR(39)+name+CHAR(39)
    from sysusers
    where issqluser = 1 and (sid is not null and sid 0x0) AND SUSER_SNAME(sid) IS NULL
    open @executequery
    fetch next from @executequery into @query
    while @@fetch_status=0
    begin
    exec (@query)
    print (@query)
    fetch next from @executequery into @query
    end
    close @executequery;
    deallocate @executequery;

    /*fix orphan users in all databases except system databases */

    DECLARE @DB_Name varchar(100)
    DECLARE @Command nvarchar(2000)
    DECLARE database_cursor CURSOR FOR
    SELECT name
    FROM sys.databases where database_id>4
    and name not like ‘%master%’

    OPEN database_cursor

    FETCH NEXT FROM database_cursor INTO @DB_Name

    WHILE @@FETCH_STATUS = 0
    BEGIN

    SELECT @Command =’
    use ‘+ @DB_Name+’;
    declare @query varchar(1000)
    declare @executequery cursor
    set @executequery=cursor for
    select ” sp_change_users_login ”+CHAR(39)+”update_one”+CHAR(39)+”,”+CHAR(39)+name+CHAR(39)+”,”+CHAR(39)+name+CHAR(39)
    from sysusers
    where issqluser = 1 and (sid is not null and sid 0x0) AND SUSER_SNAME(sid) IS NULL
    open @executequery
    fetch next from @executequery into @query
    while @@fetch_status=0
    begin
    exec (@query)
    print (@query)
    fetch next from @executequery into @query
    end
    close @executequery;
    deallocate @executequery;
    go’

    print @Command

    FETCH NEXT FROM database_cursor INTO @DB_Name
    END

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAPTCHA
Refresh

*

Primary Sidebar

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©