SQL SERVER – FIX : Error 15023: User already exists in current database.

« SQL SERVER – Function to Convert List to Table

SQL SERVER – Creating Comma Separate List From Table »

SQL SERVER – FIX : Error 15023: User already exists in current database.

February 15, 2007 by pinaldave

Error 15023: User already exists in current database.

1) This is the best Solution.
First of all run following T-SQL Query in Query Analyzer. This will return all the existing users in database in result pan.
USE YourDB GO EXEC sp_change_users_login 'Report' GO
Run following T-SQL 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 ‘ColdFusion’ is UserName, ‘cf’ 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 YourDB GO EXEC sp_change_users_login 'Auto_Fix', 'ColdFusion', NULL, 'cf' GO
Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Update_One’ links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified
USE YourDB GO EXEC sp_change_users_login 'update_one', 'ColdFusion', 'ColdFusion' GO
2) If login account has permission to drop other users, run following T-SQL in Query Analyzer. This will drop the user.
USE YourDB GO EXEC sp_dropuser 'ColdFusion' GO
Create the same user again in the database without any error.

Stored Procedure 1:
/*Following Stored Procedure will fix all the Orphan users in database by mapping them to username already exist for user on server. This SP is required when user has been created at server level but does not show up as user in database.*/ CREATE PROCEDURE dbo.spDBA_FixOrphanUsers AS DECLARE @username VARCHAR(25) DECLARE GetOrphanUsers 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 GetOrphanUsers FETCH NEXT FROM GetOrphanUsers INTO @username WHILE @@FETCH_STATUS = 0 BEGIN IF @username='dbo' EXEC sp_changedbowner 'sa' ELSE EXEC sp_change_users_login 'update_one', @username, @username FETCH NEXT FROM GetOrphanUsers INTO @username END CLOSE GetOrphanUsers DEALLOCATE GetOrphanUsers GO
Stored Procedure 2:
/*Following Stored Procedure will fix all the Orphan users in database by creating the server level user selecting same password as username. Make sure that you change all the password once users are created*/ CREATE PROCEDURE dbo.spDBA_FixOrphanUsersPassWord AS DECLARE @username VARCHAR(25) DECLARE @password VARCHAR(25) DECLARE GetOrphanUsers 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 GetOrphanUsers FETCH NEXT FROM GetOrphanUsers INTO @username SET @password = @username WHILE @@FETCH_STATUS = 0 BEGIN IF @username='dbo' EXEC sp_changedbowner 'sa' ELSE EXEC sp_change_users_login 'Auto_Fix', @username, NULL, @password FETCH NEXT FROM GetOrphanUsers INTO @username END CLOSE GetOrphanUsers DEALLOCATE GetOrphanUsers GO
Stored Procedure 3:
----Following Stored Procedure will drop all the Orphan users in database. ----If you need any of those users, you can create them again. CREATE PROCEDURE dbo.spDBA_DropOrphanUsers AS DECLARE @username VARCHAR(25) DECLARE GetOrphanUsers 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 GetOrphanUsers FETCH NEXT FROM GetOrphanUsers INTO @username WHILE @@FETCH_STATUS = 0 BEGIN IF @username='dbo' EXEC sp_changedbowner 'sa' ELSE EXEC sp_dropuser @username FETCH NEXT FROM GetOrphanUsers INTO @username END CLOSE GetOrphanUsers DEALLOCATE GetOrphanUsers GO
Reference : Pinal Dave (http://blog.SQLAuthority.com )

About these ads

Posted in DBA, Pinal Dave, SQL, SQL Authority, SQL Cursor, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | 98 Comments

98 Responses

on March 5, 2007 at 3:41 pm | Reply Mark

Excellent

Thanks for your assistance – this got me out of a hole
on March 20, 2007 at 1:05 pm | Reply Mahesh

Thanks – helped me a lot
on May 15, 2007 at 3:22 pm | Reply Pablo

The solution is very good
on June 22, 2007 at 6:47 am | Reply beZerk

i have applied solution1.
it’s perfect.

thanks a lot
on July 13, 2007 at 12:40 am | Reply Chris.J

Super!!
I just restored a db and the user couldn’t log back on afterwards. Apparently the link between the user in the db and the system was gone and this fixed it:

USE YourDB
GO
EXEC sp_change_users_login ‘Auto_Fix’, ‘ColdFusion’, NULL, ‘cf’
GO

Cheers,
Chris
on July 16, 2007 at 12:34 pm | Reply Lino Tadros

Very nice, helpful info. Thanks.
on November 22, 2007 at 1:38 pm | Reply korean 8001

oh, suprise!!
so helpful to me

thanks :)
on January 7, 2008 at 12:46 am | Reply brian

All I did was detach and attach on a different computer and the user didnt have a login and couldnt fix it.

This fixed it. I love you dude!
on January 16, 2008 at 11:38 pm | Reply isaak

Thank you. Your soultion fixed the problem.
on January 19, 2008 at 4:10 am | Reply sam

Thanks a million. This really helped me out. Great solution, you are the MAN!
on January 21, 2008 at 6:48 pm | Reply Tobias

Thanks very much!! I was searching very long for such a solution ;-)
on March 20, 2008 at 11:34 pm | Reply Max

Thanks, dude! It works
on March 27, 2008 at 8:55 pm | Reply Yazan Diranieh

You rock man!

Running
EXEC sp_change_users_login ‘Auto_Fix’, ‘username’, NULL, ‘password’

fixed my problem which happened after a database restore.
on April 16, 2008 at 9:26 pm | Reply Steven Perry

Will the above only work for SQL Server logins or can it also be applied to integrated security users as well?

Thanks.
on April 22, 2008 at 1:46 am | Reply Udo Lang

Great solution!!!
Better than any help from microsoft.

Thanks
on May 1, 2008 at 1:50 pm | Reply Manu

EXEC sp_change_users_login ‘Auto_Fix’, ‘nameofuser’, NULL, NULL

I get an Error saying

An invalid parameter or option was specified for procedure ‘sys.sp_change_users_login’.

I am trying to run this 2 fix my users on sql2005 instance. This db is currently migrated from another 2k5 instance.

Please help me out….
on May 8, 2008 at 2:08 pm | Reply Rogier

You’re the best, this fixed my problem, thanks!
on July 22, 2008 at 6:47 pm | Reply Jack

Thanks Man!!
I own you beer :)
on July 28, 2008 at 5:25 pm | Reply Vishnu

Thank a lot for this post. keep it up…
on October 9, 2008 at 5:56 pm | Reply Babilon

Thanks :) Super :)
on October 10, 2008 at 6:55 am | Reply Imran Mohammed

This should be a piece of genius.

Thanks again.
on October 14, 2008 at 4:50 am | Reply Simon

Excellent !

Your forum does the trick.

Thanks a lot….
on October 15, 2008 at 6:48 am | Reply Rob

Great thanks
on November 5, 2008 at 4:18 pm | Reply Manoj

Great,

I think the difference between your solution and other peoples is that you give us clear answers…

thnks mate..
- on November 27, 2012 at 2:43 pm | Reply carla abanes (@carlingdoodling)
  
  yup, thats why i come to this site for answers more often…not just copy-paste scripts but to also understand and learn…thanks dave!!
on December 19, 2008 at 10:17 pm | Reply Moges

Thank you for putting different options to solve the problem. sp_change_users_login ‘Auto_Fix’ .. worked for me.
on December 29, 2008 at 1:13 pm | Reply Luis

Your post helped me a lot! Thanks!
on January 6, 2009 at 11:38 pm | Reply Aldo

Great!
After HOURS wasted in the so-called-microsoft-help, i saw the light.
Clear, quick, and straight to the point.

Inb one thing M$ is great: making money. But nothing else.
on January 7, 2009 at 5:45 pm | Reply gkw

Help me fix the problem. Thanks!!!!!!
on January 9, 2009 at 9:36 pm | Reply Traced1

The T-SQL is strong in this one!

You were my first stop, fixed immediately.

/dance /train
on January 23, 2009 at 1:25 pm | Reply Sushisean

You are the MAN this really helped me out!
on January 29, 2009 at 7:26 am | Reply Indo

simply great. So neatly explained for a beginner like me.
Hats off.
on February 10, 2009 at 9:36 pm | Reply KCM

Works!!!! had to make sure I was running this in the correct Qry window under the correct DB (hehhehe) but after that it worked great! Thanks!
on February 11, 2009 at 7:28 pm | Reply Olifantje

Thanks for the solution! Another hurdle taken …

Greetz
on March 20, 2009 at 1:35 pm | Reply thato

Thanks a lot. You never disappoint
on April 21, 2009 at 3:28 pm | Reply James Ryan

Thanks a mil
That was bugging me for ages
Cheers
on May 25, 2009 at 3:46 pm | Reply Charles

Thanks – a life saver in a pinch :)
on May 28, 2009 at 6:35 pm | Reply Phil

Cheers worked prefect!
on June 11, 2009 at 1:05 am | Reply lokesh

thanks! this saved me a lot of time.
on July 5, 2009 at 12:20 am | Reply Just1

Thanks so much,

EXEC sp_change_users_login ‘Auto_Fix’, ‘ColdFusion’, NULL, ‘cf’ save me a lot of time.
on July 19, 2009 at 5:34 pm | Reply Mohammed

Thanks a lot,
very helpful , i have resolved problem
on October 16, 2009 at 1:26 am | Reply Rem

Hi,

I am trying to create a new login like this:

CREATE LOGIN [AAA\Sales] FROM WINDOWS WITH DEFAULT_DATABASE=[SalesINt], DEFAULT_LANGUAGE=[us_english]

and I am getting this error:

The server principal ‘AAA\Sales’ already exists.

This login doesn’t currently exist. AAA\Sales is a active directory distribution group. When I try to do the same thing on a different server, it works.

How I can create this user on my production sql server?

Thanks
on December 1, 2009 at 4:23 am | Reply Tony

Still working great, thanks much.
on January 6, 2010 at 3:14 am | Reply Tony

Your posting helped a lot.

Thanks.
on January 31, 2010 at 10:18 pm | Reply junaid

thx fix my problem
on February 8, 2010 at 1:33 am | Reply sandro

Thanks a lot !!
on March 3, 2010 at 1:24 pm | Reply Manpreet Singh

It’s realy help us.Thanks for help
on March 3, 2010 at 4:45 pm | Reply Deepak

I am having 25 databases and I have to delete Orphan users for all, I cannot go on each database and run the drop script. Any idea how to make it dynamic?
on March 7, 2010 at 7:58 am | Reply brenda

i love you! that worked perfectly! thanks for your post!
on March 17, 2010 at 1:48 pm | Reply BBOLAT

you’re great, thanks!
on July 3, 2010 at 3:18 am | Reply suresh

How to map all the orphaned database users to sql logins at a time. Instead of using

sp_change_users_login ‘Auto_Fix’ suresh1, suresh1 for each user
on July 8, 2010 at 1:59 am | Reply Renato

Thanks! helped me a lot
on July 14, 2010 at 10:46 am | Reply Alok

This is awesome. Great solutions.

Thanks Dude
on July 31, 2010 at 2:05 am | Reply G

Thanks for posting the solution to this, it helped us out when we ran into this problem.

I appreciate it greatly and wish MS would address stuff like this better.
on August 30, 2010 at 8:25 am | Reply Bruce

Your article is really helpful. Thank you.
on August 31, 2010 at 1:00 pm | Reply coverboy

Thanks. I really appreciate it.
on September 1, 2010 at 9:59 pm | Reply chuck

I used it. It worked! Thanks you.
on September 9, 2010 at 11:36 am | Reply Jayesh

Thanks, It resolved the issue
on September 18, 2010 at 3:51 pm | Reply SHAHID ANWAR

Dear Sir,

I am using WINDOWS 2003 and DATABASE of MS SQL 7 version with great plains programme.

Please reply me urgently.

User cannot Mark company with database.

ERROR No.1 :
[Microsoft] {ODBC SQL Server Driver}[SQL Server] Server User TAJ is not a valid user DYNAMICS

[Microsoft] {ODBC SQL Server Driver}[SQL Server] Server User TAJ is not a valid user DYNAMICS

ERROR No.2:
[Microsoft] {ODBC SQL Server Driver}[SQL Server] User or Role ‘TAJ’ already exists ijn the current database.

ERROR No.3:
[Microsoft] {ODBC SQL Server Driver}[SQL Server] User or Role ‘TAJ’ does not exists.

ERROR No.4
A get /change first operation on table Sy_User_MSTR failed / accessing SQL data.

Thanks for your cooperation in this regsrds

SHAHID
on November 28, 2010 at 7:08 pm | Reply AIR

Thank you for the script.
on December 10, 2010 at 10:11 pm | Reply Krishna murthi

Thanks a lot. Solved my problem in a min! No wonder your site came first in Google search!
on December 21, 2010 at 8:01 pm | Reply abhishek

Thanks Mate… Comprehenssive one…
on February 16, 2011 at 12:51 am | Reply Juan Carlos

He aqui una forma mas rapida para usuarios menos avanzados.

En la Tabla Sysusers, de tu DB, se almacenan todos los usuarios con acceso a ella, solo tienes que eliminar el que te da el error y luego volver a crearlo por la via correspondiente.

Espero le sirva, cualquier comentario me dejan saber.
on February 16, 2011 at 12:53 am | Reply Juan Carlos

Here is a fastest way to less advanced users.

Table sysusers, your DB, are stored all users with access to it, just eliminate that gives you the error and then recreate it through the appropriate channels.

I hope you serve, any comments let me know
on February 24, 2011 at 5:13 pm | Reply Subrahmaniam

How to avoid dropping the db user without login (new feature in 2005) while dropping the orphaned users in a database?
on April 8, 2011 at 1:28 am | Reply Dan

Thanks! This was a big help.
on April 15, 2011 at 4:45 am | Reply Martin Knazovicky

Nice – simple and effective.
EXEC sp_change_users_login ‘Report’
EXEC sp_change_users_login ‘Auto_Fix’, ‘ColdFusion’, NULL, ‘cf’
EXEC sp_change_users_login ‘update_one’, ‘ColdFusion’, ‘ColdFusion’

fixed mine problem after I created new DB from backup.
on May 3, 2011 at 8:34 am | Reply Gagan Bhargava

Thanks a lot for this great help….. Absolutely wonderful :)
on May 22, 2011 at 10:01 am | Reply tran cuong

Thanks alot, it’s really helpful
on June 27, 2011 at 9:02 pm | Reply Jim

Thanks for the post! This really fit the bill in my case….
on August 3, 2011 at 12:36 am | Reply mrmiagi

very nice. ;-)
on October 4, 2011 at 7:33 pm | Reply folies

The first “best solution” was very useful on Windows Server 2008, IIS7, SQL Server 2008. Thank you!
on November 9, 2011 at 7:52 pm | Reply Philippe Nony

Merci pour votre fix. J’ai utilisé la première solution avec succès.

Thanks for your help!
on November 17, 2011 at 10:02 am | Reply Tayyeb

I am facing one issue while installing sql 2008 standard edition i get following error message continuously tried many times uninstalling, m installing on windows 2003 standard edition and it is a backup domain controller, few week back i have installed successfully one of our backup domain controller without any problem.

Please can anyone help me solving this issue ?
on November 22, 2011 at 9:20 pm | Reply alcorspell

Thank you very much. You’re always straight to the point.
on December 9, 2011 at 1:58 am | Reply robert

Thanks, just what I had been looking for.
on January 19, 2012 at 4:32 am | Reply SQL Server – User login issue after DB Restore « Tech Hub

[...] EXEC sp_change_users_login 'update_one', '<username>', '<username>' GO Reference http://blog.sqlauthority.com/2007/02/15/sql-server-fix-error-15023-user-already-exists-in-current-da… Advertisement LD_AddCustomAttr("AdOpt", "1"); LD_AddCustomAttr("Origin", "other"); [...]
on February 21, 2012 at 7:26 pm | Reply Javier

thanks a lot !!
on February 27, 2012 at 6:13 am | Reply Asha

Excellent!!!
It made wonders…

Thanks a lot
on March 6, 2012 at 4:28 pm | Reply sari

That’s working!!! Thanks
on May 8, 2012 at 12:26 pm | Reply johnD

Simply brilliant, thank you very much, indeed.
on June 7, 2012 at 5:15 pm | Reply Suraj

Thanks a zillion. It works like a gem.
on June 7, 2012 at 7:44 pm | Reply Chella

Brilliant
on June 15, 2012 at 9:18 pm | Reply JOrge

Wonderful! That solved the problem. Thank you so much.
on June 28, 2012 at 12:58 am | Reply Stephany

a quick resolution to my problem. You’re totally awesome
on July 1, 2012 at 8:47 am | Reply Jamie

Your the best! Fixed my confluence problem, been struggling with it all day!
on August 13, 2012 at 3:21 pm | Reply iceCold

Fixed my problem, thank you. You are brilliant.
on October 4, 2012 at 1:32 am | Reply YaR

Your post helped me a lot,Thank you.
on January 2, 2013 at 6:53 am | Reply Shiro

Thanks a lot!! It solved my problem!!
on January 12, 2013 at 10:28 pm | Reply Rizwan

Great, worked perfectly, solved my problem. Thanks alot
on February 4, 2013 at 9:36 pm | Reply Waldy

dbo is a forbidden value for the login name in the procedure
on February 16, 2013 at 7:01 am | Reply SQL SERVER – Weekly Series – Memory Lane – #016 « SQL Server Journey with SQL Authority

[...] FIX : Error 15023: User already exists in the current database One of the most popular errors when SQL Server 2000 is migrated to SQL Server 2005. [...]
on February 27, 2013 at 8:00 pm | Reply Keith

I get the “new database owner is already a user” error, but when I try to run the

EXEC sp_change_users_login ‘Report’ command,
I
get no data returned. The Query completes successfully, but the panel is empty. I don’t understand where to go from here.

Thanks for your assistance.
on February 28, 2013 at 3:07 pm | Reply Vivek Bhat

Great article.. Saved my day
on March 18, 2013 at 9:00 am | Reply Chandru Maruthapan

Thanks Pinal,
for understand the problem and create the script for myself for this one..

USE Mirror
GO
DECLARE @str_query VARCHAR(max);
DECLARE @un VARCHAR(50);

CREATE TABLE #TempTable (name varchar(25))
INSERT INTO #TempTable (name)

SELECT SU.NAME AS UserName
FROM sys.sysusers AS SU LEFT JOIN sys.server_principals AS SP ON SU.name = SP.name
AND SP.type = ‘S’
WHERE SU.issqluser = 1 — Only SQL logins
AND NOT SU.[sid] IS NULL – Exclude system user
AND SU.sid 0×0 – Exclude guest account
AND LEN(SU.sid) <= 16 – Exclude Windows accounts & roles
AND SUSER_SNAME(SU.sid) IS NULL — Login for SID is null

DECLARE cur CURSOR FOR
SELECT name FROM #TempTable
OPEN cur
FETCH NEXT FROM cur INTO @un;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @str_query = 'EXEC sp_change_users_login ''AUTO_FIX'' , '''+@un+'''' + CHAR(10) + 'GO' + CHAR(10)
EXEC (@str_query)
–PRINT @str_query –TEST POINTER

FETCH NEXT FROM cur INTO @un
END
CLOSE cur;
DEALLOCATE cur;
DROP TABLE #TempTable
on April 10, 2013 at 12:05 pm | Reply Grippy

Im getting this message. I cant figure this out for the life of me. It wont let me delete the user since it owns a full text catalog in the database and I cant create user mappings since it already shows the user in the database. Please help its urgent. Thank you.

Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 214
An invalid parameter or option was specified for procedure ‘sys.sp_change_users_login’.
on April 27, 2013 at 11:17 am | Reply Andrew

For everyone getting the “An invalid parameter or option was specified for procedure” Try:
EXEC sp_change_users_login ‘Auto_Fix’, ‘username’, NULL, ‘password’

Worked for me.

Thanks!
on May 9, 2013 at 12:20 am | Reply Mike

Thank you Pinal. This was very helpful.