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

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

109 thoughts on “SQL SERVER – FIX : Error 15023: User already exists in current database.

  1. 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

  2. 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!

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

    Thanks.

  4. 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….

  5. 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.

  6. 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!

  7. Thanks so much,

    EXEC sp_change_users_login ‘Auto_Fix’, ‘ColdFusion’, NULL, ‘cf’ save me a lot of time.

  8. 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

  9. 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?

  10. 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

  11. 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.

  12. 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

  13. 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.

  14. 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

  15. 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.

  16. 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 ?

  17. Pingback: SQL Server – User login issue after DB Restore « Tech Hub

  18. Pingback: SQL SERVER – Weekly Series – Memory Lane – #016 « SQL Server Journey with SQL Authority

  19. 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.

  20. 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 0x0 — 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

  21. 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’.

  22. 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!

  23. 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.

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