<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

SQL Orphaned Username Fix

Published on
8,290 Points
2,290 Views
Last Modified:
Approved
Community Pick
What is an orphan user:

Orphaned users occur when users exists in a database but is not correlated to a server login. This can occur if a restore is done from another SQL server instance where the user existed and the login does not exist on the new server or the login was dropped.

How to fix an orphaned user:

Often times after database restores in SQL 2000, 2005, and 2008 users can become orphan. The first thing you'll notice is you cannot map a user. If you can't assign a username to a SQL database it’s because the username already exists. You can run the following query in the Query Analyzer to determine whether or not this is in fact that issue that you are experiencing.

sp_change_users_login 'report'

Open in new window


This will tell you if you have orphaned users and if so will returned the orphaned users.
If results are returned simply use the orphaned usernames to replace USERNAME in the below query. After you have made the change execute the below query.

sp_change_users_login @action = 'update_one', @usernamepattern = ' USERNAME ', @loginname = ' USERNAME '

Open in new window


If your database has multiple users you will need to execute this for each user. This should also automatically mark the user as DBO. If you need to change this afterwards you can modify this under the users properties.

How to avoid orphaned users:

To avoid orphaned users when restoring from a .bak file you should ensure that the same user(s) exist on the server you are restoring to before running the restore. The best method of restoring is to create an empty duplicate database with the same name and users and file locations to avoid complications when restoring to another instance SQL server.

Remember:

Always backup your database before making any changes!
0
Comment
0 Comments

Featured Post

Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Join & Write a Comment

Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month