Help and Support
 

powered byLive Search

BUG: Using the Auto_Fix Option with sp_change_users_login Can Leave Security Vulnerabilities

View products that this article applies to.
Article ID:298758
Last Review:November 21, 2003
Revision:4.2
This article was previously published under Q298758

SYMPTOMS

Executing the sp_change_users_login procedure with the optional Auto_Fix option may create logins with a NULL password for users without an existing corresponding login.

Back to the top

CAUSE

By using the Auto_Fix option, you are requesting that SQL Server make "best guess" matches between database users and server logins. If no appropriate login exists, you are asking it to create one, but SQL Server does not know what password you want that login to have.

Back to the top

WORKAROUND

You can work around this problem in the following ways:
•You may choose to use one of several alternate methods of synchronizing the logins as described in the following Microsoft Knowledge Base articles:
246133 (http://support.microsoft.com/kb/246133/) HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
168001 (http://support.microsoft.com/kb/168001/EN-US/) PRB: User Logon and/or Permission Errors After Restoring Dump
274188 (http://support.microsoft.com/kb/274188/EN-US/) PRB: Troubleshooting Orphaned Users Topic in BOL Incomplete
•If you do use sp_change_users_login, check its output for messages indicating that some passwords are set to NULL. Then manually change those passwords to a value that meets your security criteria. The message that you may see is as follows:
Barring a conflict, the row for user 'TestUser' will be fixed by updating its link to a new login. Consider changing the new password from null.

Back to the top

MORE INFORMATION

In versions of SQL Server 2000 that are earlier than Service Pack 3 (SP3), when you run sp_change_users_login with the auto_fix option when a corresponding logon does not exist, a logon with a NULL password is automatically created and you receive the following message:
New login created. Barring a conflict, the row for user 'User_Name' will be fixed by updating its link to a new login. Consider changing the new password from null. The number of orphaned users fixed by updating users was 0. The number of orphaned users fixed by adding new logins and then updating users was 1.
. In SQL Server 2000 SP3 and later, when you run sp_change_users_login with the auto_fix option when a corresponding logon does not exist, a logon is not created, and you receive the following message:
Server: Msg 15290, Level 16, State 1, Procedure sp_change_users_login, Line 137 Terminating this procedure. The Action 'auto_fix' is incompatible with the other parameter values ('User_Name', '(null)').
For general information about moving databases, see the following article:
224071 (http://support.microsoft.com/kb/224071/EN-US/) INF: Moving SQL Server Databases to a New Location
An additional security risk with sp_change_users_login is described in the SQL Server Books Online topic "sp_change_users_login":
"Auto_Fix makes best estimates on links, possibly allowing a user more access permissions than intended."

Back to the top

APPLIES TO
•Microsoft SQL Server 2000 Standard Edition
•Microsoft SQL Server 7.0 Standard Edition

Back to the top

Keywords: 
kbbug kbpending KB298758

Back to the top

Article Translations

 

Related Support Centers

Other Support Options

  • Need More Help?
    Contact a Support professional by E-mail, Online or Phone.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.