INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Server Security

Why can't I CREATE or DROP a USER in a database restored from another server? by tlbroadbent
Posted: 9 Jul 02 (Edited 30 Apr 03)

When moving databases from one server to another, a user's LOGIN access to these databases can be broken. Attempting to add a database USER results in a message that the USER already exists. Trying to drop the USER results in a message that the USER doesn't exist. This can be irritating and confusing.

SQL Server USERS are created in each database. Each USER is associated with a server LOGIN that is stored in the master database. The Security Identifier or SID relates the USER and the LOGIN. Under certain conditions the USER SID may not match the LOGIN SID. This can occur if the master database is rebuilt or restored and the LOGINS have to be recreated. It can also happen when databases are moved from one server to another. Though the names may match between the servers, the SIDS probably doesnÆt match. The USERS are referred to as orphaned when their SID doesnÆt match the LOGIN SID.

SQL BOL contains a topic, "Troubleshooting Orphaned USERS" which partially explains how to correct the situation and synchronize USERS to LOGINS. This topic is incomplete and some knowledgebase articles were added to provide additional information. Additional articles about orphaned USERS have been published on other Web sites.

SQL BOL: Troubleshooting Orphaned USERS
http://msdn.microsoft.com/library/en-us/trblsql/tr_servdatabse_0ttf.asp

MS KB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q274188

MS KB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q168001

MS KB: How to Resolve Permission Issues When a Database is Moved Between SQL Servers
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q240872

MS KB: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q246133

SQL BOL: sp_change_users_login
http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp

Database Joural: Fixing broken LOGINS and transferring passwords
http://www.swynk.com/friends/boyle/fixingbrokenLOGINS.asp

Database Journal: How to detect & rectify Orphaned Users in a Database
http://www.swynk.com/friends/dhingra/howtos3.asp

MS KB: Using the Auto_Fix Option with sp_change_users_login Can Leave Security Vulnerabilities
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q298758

Keywords: sp_addlogin, sp_adduser, sp_dropuser, sp_change_users_login, orphaned users, move databases, permissions, broken logins, transfer logins, passwords

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close