Ошибка 15023 sql server

if it is just one or two users, then easiest way is to drop the database user from the restored database, remap the database user to the server login using SSMS. If the server login does not exist then just create it, map the user.

Option 2: 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 : http://www.databasejournal.com/features/mssql/article.php/2228611/Migrating-Logins-from-One-SQL-Server-to-Another.htm

Code patches to help use it :
run following T-SQL Query in Query Analyzer. This will return all the existing users in database in result pan.

USE YourDB
GO
EXEC sp_change_users_login 'Report'
GO

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Auto_Fix’ attribute will create the user in SQL Server instance if it does not exist. In following example ‘ColdFusion’ is UserName, ‘cf’ is Password. Auto-Fix links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins.

USE YourDB
GO
EXEC sp_change_users_login 'Auto_Fix', 'ColdFusion', NULL, 'cf'
GO

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Update_One’ links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified

USE YourDB
GO
EXEC sp_change_users_login 'update_one', 'ColdFusion', 'ColdFusion'
GO

2) If login account has permission to drop other users, run following T-SQL in Query Analyzer. This will drop the user.

USE YourDB
GO
EXEC sp_dropuser 'ColdFusion'
GO

Create the same user again in the database without any error.

Error 15023: User already exists in current database.

Here two possible solutions to solve it.

1) This is the best Solution.

First of all run following T-SQL Query in Query Analyzer. This will return all the existing users in database in result pan.

USE YourDB
GO
EXEC sp_change_users_login 'Report'
GO

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Auto_Fix’ attribute will create the user in SQL Server instance if it does not exist. In following example ‘ColdFusion’ is UserName, ‘cf’ is Password. Auto-Fix links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins.

USE YourDB
GO
EXEC sp_change_users_login 'Auto_Fix', 'ColdFusion', NULL, 'cf'
GO

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Update_One’ links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified

USE YourDB
GO
EXEC sp_change_users_login 'update_one', 'ColdFusion', 'ColdFusion'
GO

2) If login account has permission to drop other users, run following T-SQL in Query Analyzer. This will drop the user.

USE YourDB
GO
EXEC sp_dropuser 'ColdFusion'
GO

Create the same user again in the database without any error.

Stored Procedure 1:

/*Following Stored Procedure will fix all the Orphan users in database
by mapping them to username already exist for user on server.
This SP is required when user has been created at server level but does
not show up as user in database.*/
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

Stored Procedure 2:

/*Following Stored Procedure will fix all the Orphan users in database
by creating the server level user selecting same password as username.
Make sure that you change all the password once users are created*/
CREATE PROCEDURE dbo.spDBA_FixOrphanUsersPassWord
AS
DECLARE @username VARCHAR(25)
DECLARE @password 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
SET @password = @username
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username='dbo'
EXEC sp_changedbowner 'sa'
ELSE
EXEC sp_change_users_login 'Auto_Fix', @username, NULL, @password
FETCH NEXT
FROM GetOrphanUsers
INTO @username
END
CLOSE GetOrphanUsers
DEALLOCATE GetOrphanUsers
GO

Stored Procedure 3:

----Following Stored Procedure will drop all the Orphan users in database.
----If you need any of those users, you can create them again.
CREATE PROCEDURE dbo.spDBA_DropOrphanUsers
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_dropuser @username
FETCH NEXT
FROM GetOrphanUsers
INTO @username
END
CLOSE GetOrphanUsers
DEALLOCATE GetOrphanUsers
GO

The SQL Server Error 15023 arises during customer arranging when you restore an informational collection to a substitute server. Support and restore are fundamental tasks in SQL Server. The essential issue is the customer arranging that may never again work when you restore the database to a substitute server.

If you try to remap the customer, you may end up with a slip-up. Around here at ARZHOST, we have seen a couple of establishments for this misstep while exploring SQL issues as an element. “Microsoft SQL Server Error 15023” our Server Management Services for the web has and online expert communities.

Today at arzhost.com, we’ll explore the justification behind this screw-up and how to fix it.

More concerning SQL Server Error 15023

In SQL Server, support and restore are clear tasks. The fundamental issue that arises here is that customer arranging doesn’t work when you restore the informational collection to a substitute server. “Microsoft SQL Server Error 15023” Expecting you endeavor to design informational collection to a customer by going to

• Security >> Logins >> right snap some customer >> Properties >> User Mapping >> Select DB >> set as db_owner and a short time later okay, the going with error is gotten as shown in picture:

What causes SQL Server Error 15023 to occur?

Regularly, when we support and restore informational indexes across the SQL servers. “Microsoft SQL Server Error 15023” we are simply restoring a customer informational collection and not the master informational collection where logins are kept.

Ensuing to restoring we guess that the restored informational index should work much the same way as the support. Notwithstanding, the login miss the mark for a customer that had approvals in the maintained up the database. This issue is caused because of Security recognizing confirmation numbers (SID) that are muddled or ‘abandoned’ in the sysusers table.

How do we fix SQL Server Error 15023?

Lately, one of our customers pushed toward us with a comparable error message. By and by, we ought to see how our Hosting Expert Planners settle this slip-up.

1) Initially, we ran the going with T-SQL Query in Query Analyzer. This will return all of the current customers in the database in result skillet:

• USE Your DB
• GO
• Leader sp_change_users_login 'Report'
• GO

To relate login with the username, we ran the going with T-SQL Query in Query Analyzer. Here, the ‘Auto Fix’ property will make the customer in SQL Server case in case it doesn’t exist.

In like manner, in the inquiry, we invigorated the ‘Username’ with genuine customer username and ‘Secret expression’ with certified Password. “Microsoft SQL Server Error 15023” Auto-Fix interfaces a customer section in the sysusers table in the current informational collection to a login of a comparative name in sysxlogins.

• USE Your DB
• GO
• Leader sp_change_users_login 'Auto Fix', 'Username', NULL, 'Secret word'
• GO

To relate login with the username, we ran the going with T-SQL Query in Query Analyzer. Here, ‘Update One’ will connect the foreordained customer in the current database to log in. Here, login ought to at this point exist, customer and login ought not to be set in stone and mystery expression should be NULL or not shown.

• USE Your DB
• GO
• Leader sp_change_users_login 'update one', 'ColdFusion', 'ColdFusion'
• GO

2) We ran the going with T-SQL in Query Analyzer since the login account had agreed to drop various customers. This inquiry will drop the customer:

• USE Your DB
• GO
• Chief sp_dropuser 'ColdFusion'
• GO

Then, “Microsoft SQL Server Error 15023” we made a comparative customer again in the database with no error.

Termination

Along these lines, “Microsoft SQL Server Error 15023” This SQL error can arise during customer arranging when you restore a database to a substitute server. Today at arzhost.com, we saw the objective to this SQL screw-up.

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into «Error ‘15023’ User or role already exists in the current database» when you use the «User Mapping» SQL Management Studio functionality to allow necessary user permissions to the new database. This is puzzling enough since a database backup should bring us back to the original state. This is caused by Security Identification numbers (SID) that are mismatched or ‘orphaned’ in the sysusers table.

To resolve this problem, we can make use of the SQL Server stored procedure
sp_change_users_login.

From MSDN, the syntax of
sp_change_users_login is:

sp_change_users_login [ @Action= ] ‘action’

    [ , [ @UserNamePattern= ] ‘user’ ]

    [ , [ @LoginName= ] ‘login’ ]

    [ , [ @Password= ] ‘password’ ]

[;]

1. Switch to the target database

use mydatabasename
                      

Open in new window

2. List problematic SID

Lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login. user, login, and password must be NULL or not specified. This step is informative and thus
optional.

exec sp_change_users_login @Action='Report'
                      

Open in new window

3. Fix problematic SID

Links the specified user in the current database to an existing SQL Server login. User and login must be specified. password must be NULL or not specified. The login name(s) to provide is/are from the result reported in step 2.

exec sp_change_users_login @Action='Update_One', @UserNamePattern='MyLoginID', @LoginName='MyLoginID'
                      

Open in new window

Repeat running ‘Update_One’ statement for all login names.

4. Verify all is okay now

Repeat the sql statement with Action=’Report’ as described in step 2 to confirm we have resolved all login names. If no records are returned after running Action=’Report’, we are 99% there!

Now, try logging in using the «just-fixed» SQL user login id to confirm we have resolved «Error ‘15023’».

TIP: You may wish to save the your sql statements into a .sql file so you can reuse it in future database migration/restore.

MSDN Reference:
http://msdn.microsoft.com/en-us/library/ms174378.aspx

To view the original article and discussion you can visit my blog
here.

Cheers

hongjun