Creating Logins for Orphaned SQL Server Users

There are cases where you might need to restore an entire SQL Server database (i.e. in the case of loss of data, etc.). A set of objects that are restored from the backup set and are included in the restored database are the database users.

Though, when speaking about SQL Server logins, these users will might be orphaned as the corresponding SQL Server logins might not exist anymore. A similar case is when restoring a database on another SQL Server Instance.

In this case, you can use the following stored procedure for fixing such issues: sp_change_users_login

USE AdventureWorks
EXEC sp_change_users_login 'Auto_Fix', 'UserName', NULL, 'Password';

The above example will create a new SQL Server Login for the given user name and use as a password the given password string.

You can also assign existing logins to orhaned users.

For more information you can visit SQL Server Books Online.

Labels: ,