Orphan User in SQL Server

Orphan users are the users which are available in the database level but their mapped logins not available in the server level. Orphan users are created when a database is restored from backup from one server on another server.

To get the Orphan users in any database in SQL Server use below :

Syntax :

USE DATABASENAME
EXEC sp_change_users_login report
GO

Example –
Let us assume we have restored GeeksDb from Server1 to Server2, using below command in Server2.

USE GeeksDb
EXEC sp_change_users_login report
GO 

Output –



UserName UserSID
Geek1 0x7A4X871C3EXX7C42X67B5F3CD2C35FXX
Geek2 0x7A4E871C3EXX27C42B67XXF3CC4C35FXX
Geek3 0x7A5E871X3EXX27C42F57XXF3CC4C35FXX
Geek4 0x7A5E871C3EFF27C32D67XXF3CC4C45FXX

Below methods could be used to fix Orphan users.

  1. USING WITH ORPHANED USER SID :
    To fix any orphaned users, use create login by using SID.

    Syntax :

    USE MASTER
    CREATE LOGIN [LoginName] 
    WITH PASSWORD = 'Password',
    SID = 0x7A4X871C3EXX7C42X67B5F3CD2C35FXX 

    Example –

    USE MASTER
    CREATE LOGIN [Geek1] WITH PASSWORD = 'Pa$$W0rd1', 
    SID = 0x7A4X871C3EXX7C42X67B5F3CD2C35FXX 
    
  2. USING UPDATE_ONE :
    UPDATE_ONE could be used to map even when Login name and User name are different or could be used to change user’s SID with Logins SID.

    First, create new login.



    USE MASTER
    CREATE LOGIN [LoginName] WITH PASSWORD = 'Password'

    Once login is created use UPDATE_ONE to fix orphan user.

    Syntax :

    USE DATABASENAME
    sp_change_users_login UPDATE_ONE, 'UserName', 'LoginName'
    GO

    Example –

    USE MASTER
    CREATE LOGIN [Geek2] WITH PASSWORD = 'Pa$$W0rd2'
    USE GeekDb
    sp_change_users_login UPDATE_ONE, 'Geek2', 'Geek2'
    GO
    
  3. USING AUTO_FIX –
    TYPE 1 : When Login Name and User Name are same.

    First create the login and then assign login SID to Orphan User.

    Syntax :

    USE master
    CREATE LOGIN [LoginName] WITH PASSWORD = 'Password'
    
    USE DATABASENAME
    sp_change_users_login AUTO_FIX, 'LoginName/UserName'
    Go

    Example :

    USE master
    CREATE LOGIN [Geek3] WITH PASSWORD = 'Pa$$W0rd3'
    USE GeekDB
    sp_change_users_login AUTO_FIX, 'Geek3/Geek3'
    Go 

    TYPE 2 : Without creating the login.

    Syntax :

    USE DATABASENAME
    sp_change_users_login AUTO_FIX, 'UserName', NULL, 'Password'
    GO

    Example :

    USE GeekDb
    sp_change_users_login AUTO_FIX, 'Geek4', NULL, 'Pa$$W0rd4'
    GO 

    To get the Orphan users in any database after using above methods :

    USE GeeksDb
    EXEC sp_change_users_login report
    GO 

    Output –

    1. UserName 2. UserSID

    Once the orphan users are fixed successfully, there will not be any orphan user (UserName and UserSID) as the result of above command.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.