[MSSQL] ERROR 15023 에러

If you've restored a database onto another server, you may get the following error message when adding user logins to the database:

Create failed for User 'TMS'. (Microsoft.SqlServer.Smo)

User, group, or role 'TMS' already exists in the current database. (Microsoft SQL Server, Error: 15023)

To fix this there are two different methods depending on the version of SQL Server you are using. Both of these commands re-map the user's Security Identifier (SID) to match the sql server login's SID.

 

SQL Server 2008 / SQL Server 2008 R2

If you have already created the server login for the user, run this in the database with the login problem. (Change 'user' to be the database username you wish to fix, and 'serverlogin' is the sql server login to map the user to).

 ALTER USER user WITH LOGIN = serverlogin

For full details of the ALTER USER command see MSDN http://msdn.microsoft.com/en-us/library/ms176060.aspx

 

SQL Server 2005 / SQL Server 2000

If you have already created the server login for the user, run this in the database with the login problem. (Change 'user' to be the username you wish to fix).

EXEC sp_change_users_login 'Auto_Fix', 'user'




Note that sp_change_users_login has be deprecated in SQL Server, future versions will require using ALTER USER.

For full details of sp_change_users_login see MSDN http://msdn.microsoft.com/en-us/library/ms174378.aspx