Creating Logins for Orphaned SQL Server Users

Creating Logins for Orphaned SQL Server Users

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
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

Example
USE AdventureWorks
GO
EXEC sp_change_users_login ‘Auto_Fix’, ‘UserName’, NULL, ‘Password’;
GO

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.


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.