Related Pages

RSS Feed Knowledge Base Feed

Page Options
Twitter:
Sponsor:


Orphaned Users in MS SQL Server 2000

Problem Description:

When you view the details of a database user in Microsoft SQL server 2000 you will see that the login name displays as "<none>".

This is also known as an "Orphaned User"



Cause of the Problem:

This usually occurs when a database is backed up from a server and restored to another server without restoring the master database.

As a result of this the actual login name that is stored in the master database does not exist and cannot be linked with the user account already existing in the database.



Advertisement:
Your Ad Here

Resolution/Fix:

You will need to create the user account that is missing in the master database and then relink it to the user account in the database you restored.

It is recommended that the new user account that you create match the old user account name for simplicity and to easily inherit any objects that user account owned.

REMEMBER TO BACKUP YOUR DATABASE BEFORE PERFORMING ANY OF THESE STEPS

  1. Open the SQL Query Analyser and select the database that has the orphaned account in it.
  2. Run the following code where USRACCT is the name of the user account that is orphaned
    sp_change_users_login 'update_one', 'USRACCT', 'USRACCT'
  3.  Next it is a good idea to reset the password on the account to make sure the accounts are properly synced. Use the following code to set the password. Again replace USRACCT with the user name and PSWD with the password you wish to set.
    Use master go sp_password 'PSWD', 'ok', 'USRACCT'




Related Programs and Devices:
Microsoft SQL Server 2000 , SQL , Database

Last Updated: 05/27/2008 01:50 AM

blog comments powered by Disqus