This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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.
|
1 2 3 4 5 6 7 8 9 |
SELECT [dp].[type_desc] ,[dp].[SID] ,[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.
|
1 2 |
SELECT [sid] FROM sys.sql_logins; |
Step 3: Create manual login and map your missing SIDs:
|
1 2 3 |
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:
|
1 |
ALTER USER MyUser123 WITH Login = Login4Orphaned; |
