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 (https://blog.sqlauthority.com )
129 Comments. Leave new
I found this tips more usefull than official MS suggestions.
Thanks.
thank you
Description: Checks that the DB user that you provided is the default DBMS admin user which has all permissions required by MDM. The default admin users are: (1) Oracle=system (2) MS SQL=sa (3) MaxDB=dba (4) IBM DB2= db2admin. Note: This only checks that you provided the default admin user; however when connecting the MDS to the DBMS you may use a different user that has the same permissions as the default admin user.
Status: WARNING (Recommendation: Apply the proposed solution)
Details: Specified DBMS user ‘mddadm’ may not have sufficient permissions required by MDM in order to connect to the database
Solution: Check that the DBMS user ‘mddadm’ has the same permissions as the default user (sa). Alternatively, check with your DB system admin if you can use the default user instead.
How can we fix for domain user? [Domain\UserID].
super duper
Thanks Mr. Pinal Dave for posting script to solve to error 15023
Thx, it works. :)
almost 8 years … and still the best solution around! Thank you!
I have same At the time of import application in ISS server I have put details of error Please check and tell the solution of this problem
the error is below:
The package installation failed.
Details:
An error occurred during execution of the database script. The error occurred between the following lines of the script: “1” and “3”. The verbose log might have more information about the error. The command started with the following:
“CREATE USER [NT AUTHORITYNETWORK SERVICE] FOR LOG”
User, group, or role ‘NT AUTHORITYNETWORK SERVICE’ already exists in the current database. https://support.microsoft.com/en-us/help/2023856/you-receive-a-script-error-message-when-you-use-the-web-deployment-too
User, group, or role ‘NT AUTHORITYNETWORK SERVICE’ already exists in the current database.
excelent, you helpme a lot!!!
life saver
It worked for me. Thanks!
Glad that it helped you André.
Thank you, the post was really helpful. I’m curious what could have caused the login and user to unattach from each other. Can you tell me that?
User is part of database and Login is part of system database (master) so backup/restore normally causes it.
Thumbs up, great help
Thanks a bunch! This worked for me.
Thank you very much, it solved the issue I was having!
Still working in 2016! Thanks much and Happy New Year.
This is the Best. Thank You!
Thanks! It’s save time for me
From 10 years in the future – Thanks!
Mike :)
This worked for me. Great job !!