<

SQL Orphaned Username Fix

Published on
8,269 Points
2,269 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

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Join & Write a Comment

Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month