How to fix Orphaned Users in database


FIX : Error 15023: User already exists in current database

Introduction
Orphan user is a user in a SQL Server database that is not associated with a SQL Server login..


The below query will return all the existing orphaned users in database.


USE MyDB
GO
EXEC sp_change_users_login 'Report'
GO

Run following Query in Query Analyzer to associate login with the username. ‘Auto_Fix’ attribute will create the user in SQL Server instance if it does not exist. In following example ‘Myuser’ is UserName, ‘abc’ is Password. Auto-Fix links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins.


USE MyDB
GO
EXEC sp_change_users_login 'Auto_Fix', 'MyUser', NULL, 'abc'
GO

‘Update_One’ links the specified user in the current database to login. login must already exist. user and login must be specified.


USE My_Database
GO
EXEC sp_change_users_login 'update_one', 'Myuser', 'Myuser'
GO

Use below code to fix all the Orphan User issue


DECLARE @username VARCHAR(25)
DECLARE fixOrphanusers CURSOR
FOR
SELECT
UserName = name FROM sysusers
WHERE issqluser = 1 AND (sid IS NOT NULL AND sid 0x0)
AND
SUSER_SNAME(sid) IS NULL
ORDER BY name
OPEN fixOrphanusers
FETCH NEXT FROM fixOrphanusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC
sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixOrphanusers
INTO @username
END
CLOSE
fixOrphanusers
DEALLOCATE fixOrphanusers

Advertisements

4 Responses

  1. First, make sure that this is the problem. This will lists the orphaned users:

    EXEC sp_change_users_login ‘Report’

    If you already have a login id and password for this user, fix it by doing:

    EXEC sp_change_users_login ‘Auto_Fix’, ‘user’

    If you want to create a new login id and password for this user, fix it by doing:

    EXEC sp_change_users_login ‘Auto_Fix’, ‘user’, ‘login’, ‘password’

    Alfredo Luis

  2. Another one is, If instance has many accounts then run below script and copy the output and execute it

    Select ‘Exec sp_change_users_login ”Update_One”,”’+su.name+”’,”’+su.name+””
    From master.dbo.syslogins sl
    Inner Join dbo.sysusers su
    On sl.name = su.name
    Where su.uid > 2

    Arun Halyal

  3. Why to take such a pain to write SQL Command and a loop to fix all the orphend login

    Use this sp_change_users_login @Action = ‘AUTO_FIX’

    sufian

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: