Learn Sql Server   Learn Sql Theory   Learn Sql

 
Chapter 1
Chapter 2
Chapter 3
Chapter 4
Chapter 5
Chapter 6
Chapter 7

Sql Server web services

Sql Server HTTP Endpoints

Creating an HTTP Endpoint

Managing HTTP Endpoint Security

Modifying an HTTP Endpoint

Removing an HTTP Endpoint

Reserving an HTTP Namespace

Sql Server Error Handling

Creating a User-Defined Error Message using sp_addmessage

Dropping a User-Defined Error Message Using sp_dropmessage

Sql Server RAISERROR

Using TRY CATCH

Error Handling with TRY...CATCH

Nesting TRY...CATCH Calls

Sql Server Principals

Creating a Windows Login

Viewing Windows Logins

Altering a Windows Login

Dropping a Windows Login

Denying SQL Server Access to a Windows User or Group

Sql Server Authentication

Creating a SQL Server Login

Viewing SQL Server Logins

Altering a SQL Server Login

Dropping a SQL Login

Managing Server Role Members

Reporting Fixed Server Role Information

Database Principals

Creating Database Users

Reporting Database User Information using sp_helpuser

Modifying a Database User

Removing a Database User from the Database

Fixing Orphaned Database Users

Reporting Fixed Database Roles Information

Managing Fixed Database Role Membership

Managing User-Defined Database Roles

Managing Application Roles

Chapter 8
Chapter 9
Chapter 10
Chapter 11
Chapter 12
Chapter 13
Chapter 14
Chapter 15
Chapter 16

Fixing Orphaned Database Users

When you migrate a database to a new server (by using BACKUP/RESTORE, for example) the relationship between logins and database users can break. A login has a security-identifier (sid) which uniquely identifies it on the SQL Server instance. This sid is stored for the login’s associated database user in each database that the login has access to. Creating another login on a different SQL Server instance with the same name will not recreate the same sid.
The following query demonstrates this link by joining the sys.database_principals system catalog view to the sys.server_principals catalog view on the sid column:

SELECT s.name LoginName, d.name DbName, d.sid
FROM sys.database_principals d
INNER JOIN sys.server_principals s ON
d.sid = s.sid
WHERE s.name = 'Veronica'

This returns:


LoginName DbUserName sid
Veronica Veronica 0xEC5C372109E10344BC6CE7B04514EC8D

(1 row(s) affected)

If you RESTORE a database from a different SQL Server instance onto a new SQL Server instance— and the database users don’t have associated logins on the new SQL Server instance—the database users can become “orphaned.” If there are logins with the same name on the new SQL Server instance that match the name of the database users, the database users still may be orphaned in the database if the login sid doesn’t match the restored database user sid.
To fix this for SQL logins, you can use the sp_change_users_login system stored procedure, which uses the following syntax:

sp_change_users_login [ @Action = ] 'action'
[ , [ @UserNamePattern = ] 'user' ]
[ , [ @LoginName = ] 'login' ]
[ , [ @Password = ] 'password' ]

Arguments


Action

If selecting auto_fix, database users and logins will automatically be linked if they have the same name.
The report option lists orphaned users in the current database.
The update_one option explicitly links a user in the current database to an existing SQL Server login.

User

The database user name.

Login

The SQL Server login name.

Password

This is the password that the new SQL Server login will use if there is no matching login name on the SQL Server instance. This system stored procedure will link those names that match, and create new SQL logins for those orphaned database users that don’t match.

In this example, a database called TestDB was restored to a new SQL Server instance. TestDB has a user named Danny. There isn’t a login named Danny on the new SQL Server instance, so the database user is orphaned. To detect this, sp_change_users_login with the report action is used:

EXEC sp_change_users_login 'Report'

This returns:

UserName UserSID
Danny 0x87424DA9CF8EF548AE58E19BD04A798D

Next, the sp_change_users_login is executed with the auto_fix action to fix the orphaned database user:

EXEC sp_change_users_login 'Auto_Fix', 'Danny', NULL, 'newDannypassword!#@'

This returns:


Barring a conflict, the row for user 'Danny' will be fixed by updating its link to a new login.
The number of orphaned users fixed by updating users was 0.

How It Works


This recipe demonstrated how to use sp_change_users_login to fix orphaned database users. The first query executed the procedure with the Report option in order to show any orphaned users:
EXEC sp_change_users_login 'Report'
After that, the stored procedure used the Auto_Fix option to link a specific database user (Danny):
EXEC sp_change_users_login 'Auto_Fix', 'Danny', NULL, 'newDannypassword!#@'
A password was provided in the fourth parameter so that if an existing login named Danny isn’t found, it will be created and will use the provided password. The results of that operation tell us that no existing logins were found (“the number of orphaned users fixed by updating users was 0”).
Instead, a new login was created (“the number of orphaned users fixed by adding new logins and then updating users was 1”).
The number of orphaned users fixed by adding new logins and then updating users was 1.

Sponsored Links