SQL Server 2008 R2 error: 15023, user, group, or role already exists

Question

I have a test database that I'm having permissions issues with.
I can't access the reporting database, and the application's help documentation says to do the following:

Resolution: 

1. Launch the SQL Server Management Studio and connect to the database server(s) hosting   the Vision and Reporting Server databases. 
2. Expand the security folder. 
3. Select logins and right click on the <username> user and choose properties. 
4. Click the User Mapping tab 
5.Make sure the following databases are selected in the Users mapped to this Login:

    ReportServer
    ReportServerTempDB
    Your Vision databases
    This maps the login/user to the respective databases.
6. As you select each database (including your Vision database), select the db_owner role in the Database role membership for: section. You must select this option for each database.

When I do this, I get the following error:

"Create failed for user '<servername>\<username>'.  User, group, or role '<servername>\<username>' already exists in the current database. (Microsoft SQL Server, Error: 15023)"

I have googled this error and tried the following command on each database:

 ALTER USER [<username>] WITH LOGIN = [<username>] 

The message indicated that the commands completed successfully but I am still getting the above error when I try to map each database as instructed above.

What am I missing?

Per Kin's comment (thanks) I tried this: - I right clicked the user and selected: Script Login As > Drop and Create To > New query window. - I ran the resulting query and tried to map the user roles by selecting the other two databases and db_owner again but I am still getting the same error message as above.

Thoughts??

Answer 1

There are a number of things you can try, and the success of each will probably depend on what server role you have.

For starters, if it is just one or two users then honestly, the easiest way is to drop the database user from the restored database, and providing there is an existing server login already, remap the database user to the server login using SSMS. If the server login does not exist then just create it, map the user, and presto! Away we go.

Next option: If you are migrating a large number of users, use sp_help_revlogin. sp_help_revlogin is a Microsoft supplied stored procedure that will help migrate logins from one server to another, including passwords and SIDs. Here is a good article about it SP_HELP_REVLOGIN

Answer 2

I use Auto_Fix with sp_change_users_login in my dev environment to fix such issues (Error 15023). Avoid using Auto_Fix in security-sensitive situations.

Auto_Fix: Links a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name. If a login with the same name does not exist, one will be created. Examine the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.

sp_change_users_login 'AUTO_FIX', 'myuser'

Also, note that this feature may be removed in a future version of Microsoft SQL Server.

Other References:

1. SQL Server 2008 user, group, or role already exists in the current database
2. FIX : Error 15023: User already exists in current databas
3. SQL map a login to an existing user

Answer 3

ALTER USER [<username>] WITH LOGIN=[<username>]

Is the right approach.

Otherwise go to:

Security > Logins > (your username) > Properties > User Mapping

and remap that user to the database you want.

You can use the following query in the context of your database to check for orphans:

select
    dp.name [user_name]
    ,dp.type_desc [user_type]
    ,isnull(sp.name,'Orhphaned!') [login_name]
    ,sp.type_desc [login_type]
from   
    sys.database_principals dp
    left join sys.server_principals sp on (dp.sid = sp.sid)
where
    dp.type in ('S','U','G')
    and dp.principal_id >4
order by sp.name

Answer 4

go to the master database and delete the user