In this post, I am going to share one of the common error which is faced by most of the SQL Server DBA when they are migrating database from one server to another server.
Error 15023: User already exists in the current database. This basically the problem of Orphan users, which exists as a Database Login, but no longer exists in Master Database.
For example, I have migrated one database and user “dbrnd” exists in that database. In that new server, now I am going to create “dbrnd” login with assignment of that database.
Which threw an error like user already exists in the current database.
The simple solution is to map SID between Database login and Master Login.
Step 1: Identified orphaned users of your restored database.
,[dp].[name] AS UserName
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
ON [dp].[SID] = [sp].[SID]
WHERE [sp].[SID] IS NULL
AND [dp].[authentication_type_desc] = 'INSTANCE';
Step 2: Select any of that SID and try to find in sys.sql_logins table. If you do not find any match record, that user is orphaned.
Step 3: Create manual login and map your missing SIDs:
CREATE LOGIN Login4Orphaned
WITH PASSWORD = 'test123*1',
SID = 0x088232135A0AAF44A2D1B18DCC280E44;
Step 4: Now, create user for this and a make your orphaned user as specific user:
ALTER USER MyUser123 WITH Login = Login4Orphaned;
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.