How to Create Logins for Orphaned Database Users in SQL Server

There are cases where you might need to restore an entire SQL Server database (i.e. in the case of loss of data, etc.) on another SQL Server instance. A set of objects that are restored from the backup set and are included in the restored database are the database users. However, in such cases, the SQL Server logins are not restored, since they are server-level objects. This article explains how to create logins for orphaned database users in SQL Server.

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

 

Syntax example for creating a login for an oprhaned database user in SQL Server

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 orphaned users, again by using the sp_change_users_login stored procedure.

 

Find and Handle Orphaned Database Users with DBA Security Advisor

DBA Security Advisor, is our SQL Server security tool, which can help you analyze your SQL Server Instances for security misconfigurations, and get useful recommendations.

SQL Server Security Tool - DBA Security Advisor - Security Checks

The way it works, is that you select the security checks you want to perform, you then connect to one or more SQL Server instances, run the assessment, and you get a comprehensive report with recommendations.

One of the security checks, is checking for orphaned users.

Try DBA Security Advisor free for 14 days!

 


Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips
(special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Enroll Now with Discount!


 

Featured Online Courses:

 

Read Also:

 

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHubTV)!

Like our Facebook Page!

Check our SQL Server Administration articles.

Check out our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub