Wednesday, February 20, 2013

User Group or Role already exists in the current database

When trying to create user with login or edit user mapping…etc, you may receive below error message.
Error 15023: User, Group or Role already exists in the current database
You may see below error when you are dealing with group

Error 15024: The group already exists in the current database
Users will receive this message when user existed without a SQLServer login in the database.

If the user does NOT exist, then you can run below command using “auto_fix” attribute to create the user in the database.

USE DB_NAME
GO
EXEC sp_change_users_login 'auto_fix', 'USER_NAME'
GO
You will see the output with following result
The row for user will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.

If the user is ALREADY exists and if you want to map login with the user run below command using “updated_one” attribute.


USE DB_NAME
GO
EXEC sp_change_users_login 'update_one', 'USER_NAME', 'LOGIN_NAME’
GO
You can also use below Stored Procedure to fix all the Orphan users in database by mapping them to username which is already exist on server. Make sure you test the script in a testing environment first.

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
You can refer Microsoft Link for more information
http://msdn.microsoft.com/en-us/library/ms175475.aspx

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

No comments:

Post a Comment