User already exists in current database. SQL Server Error 15023

Leave a comment (0) Go to comments

This error usually happens after you have restored a database from a backup from one server to another. When you move a databases from one SQL Server to another, and your application might get errors saying “Login failed for <user>” and when you check Database Security and you find that login was already in the database with appropriate rights to allowed objects in the database but you still end up this Error.

User already exists in current database. SQL Server Error 15023 sql backup and restore database restore sql database from backup

Error 15023: User already exists in current database.

Cause : The problem occurs because database users which you see inside the database are “orphan“. Orphan users are those users for which there is no login id associated with this database user. This is true even if there is a login id that matches the user, since there is a GUID (security ID SID) that has to match as well. This can be easily understood by following picture,


User already exists in current database. SQL Server Error 15023 sql backup and restore database restore sql database from backup

This picture has been taken from Jonathan

User – means a user which you find inside a database

LOGIN – A login credentials which you see in security section of Server and use to connect with SQL Server.

Solution / FIX :

The solution is pretty simple, we need to make sure that SID of Login (Server Principal) matches with SID of database user.

To do so we have multiple Options

SOLUTION 1 : Maps an existing database user to a SQL Server login One by One

ALTER USER <Database_User_name> WITH LOGIN = <SQL_Server_LoginName>

Execute the above code, to make a mapping between database user and server login. In case you are using SQL Server 2005 SP1 and older versions then use the following SP

EXEC sp_change_users_login 'Update_One', '<DB_USER_NAME>','<SQL_Server_Login_NAME>';

 

IF you want to find all Orphan Users in current database, Database Users that are no link to SQL Server Login, use the following SQL statement.

SQL statement, which will list all Orphan Users

USE <YourDB_NAME>
GO
EXEC sp_change_users_login 'Report'
GO

 

In case you have multiple Orphan users, then it’s pretty time consuming to map user to login one by one, to overcome this problem, we can do all at once using following TSQL statement

DECLARE @username varchar(50)
DECLARE fixusers CURSOR FOR
SELECT UserName = name FROM master..sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers

 

this TSQL statement, will check all Logins, weather they have a similar name Database User or not, if it found, this will map the login with user.

NOTE - this Script might give you some errors, if a login doesn’t have a corresponding user, which should be fine and you can ignore those errors, as it has already fixed, what was matched.

Solution 2 :

Drop the Database User and create again and give necessary permission.

EOF - User already exists in current database. SQL Server Error 15023, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

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


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.