Apex Customer Portal Apex Customer Portal
Forums Pictures Articles Downloads
Advanced purchasing for Electrical, Plumbing, and HVAC contractors
Register Active Topics Members Search Bookmarks FAQ
Username:
Password:
Save Password Forgot your password?
 
 All Forums
 Microsoft SQL Server
 Problem: Users are "orphaned" after database move
 Printer Friendly Version  
Author Previous Topic Topic Next Topic  
admin
Admin



132 Posts
Posted - April 16 2008 :  10:17:38 AM  Show Profile Send a private message to admin

Problem


Microsoft has a very good knowledge base article about moving your SQL Server database here. Step 3 in this article refers to solving the problem of "orphaned" users:
quote:

After you transfer logins and passwords to the destination server, users may be unable to access the database. Logins are associated to users by the security identifier (SID), and if the SID is inconsistent after you move a database, SQL Server may deny the user access to the database. This problem is known as an orphaned user. If you transfer logins and passwords by using the SQL Server 2000 DTS Transfer Login feature, you will probably have orphaned users. Additionally, integrated logins granted access on a destination server in a different domain than the source server cause orphaned users.


Solution


Execute the following steps on the database server using the SQL Query Analyzer.

1. First, identify your orphaned users. Execute the following script:


USE Apex
GO
exec sp_change_users_login 'Report'
GO

The results of this query will be a list of your orphaned users.

2. If the database owner (DBO) is listed as orphaned, run this script:


USE Apex
exec sp_changedbowner 'sa'

3. For each of the orphaned users that were mentioned in step 1, run the following script:


USE Apex
GO
EXEC sp_change_users_login 'Auto_Fix', '<user name>', NULL, '<password>'
GO

Replace <user name> with the user's name in single quotes, and <password> with the user's password (also in single quotes). You will need to know each user's password to do this procedure. Passwords are case sensitive.


Jump To:

Set as your default homepage Add favorite Privacy   2018 Vulcan Software LLC All Rights Reserved.   Go To Top Of Page