KB168001 - User logons and permissions on a database may be incorrect after the database is restored

Article ID: 168001

Article Last Modified on 3/28/2006

Microsoft SQL Server 6.0 Standard Edition
Microsoft SQL Server 6.5 Standard Edition
Microsoft SQL Server 7.0 Standard Edition
Microsoft SQL Server 2000 Standard Edition
Microsoft SQL Server 2005 Standard Edition
Microsoft SQL Server 2005 Developer Edition
Microsoft SQL Server 2005 Enterprise Edition
Microsoft SQL Server 2005 Express Edition
Microsoft SQL Server 2005 Workgroup Edition

This article was previously published under Q168001

SYMPTOMS

If a dump of a SQL Server user database is restored to a different SQL Server (such as a hot backup server) or to the same SQL Server after either rebuilding or reloading an old version of the master database, user logons and permissions on the database may be incorrect.

This problem may reveal itself in several ways:

While logging on to a 6.x server, users may receive the following error:
Msg 4002, Level 14, State 1, Server Microsoft SQL Server, Line 0
Login failed
DB-Library: Login incorrect.
While logging on to a 7.0 server, users may receive the following error:
Msg 18456, Level 14, State 1,
Login failed for user '%ls'.
While trying to access objects within the database, users may receive the following error:
Msg 229, Level 14, State 1
%s permission denied on object %.*s, database %.*s, owner %.*s
While attempting to create a login and grant access to the restored database, or add the user to the database, the following error may be received:
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.
Users may have permissions on objects for which they previously did not.

CAUSE

User logon information is stored in the syslogins table in the master database. By changing servers, or by altering this information by rebuilding or restoring an old version of the master database, the information may be different from when the user database dump was created. If logons do not exist for the users, they will receive an error indicating "Login failed" while attempting to log on to the server. If the user logons do exist, but the SUID values (for 6.x) or SID values (for 7.0) in master..syslogins and the sysusers table in the user database differ, the users may have different permissions than expected in the user database.

Note If you are using Microsoft SQL Server 2005, the syslogins table and the sysusers table are implemented as compatibility views. These views are sys.syslogins and sys.sysusers. For more information about compatibility views, see the "Compatibility Views (Transact-SQL)" topic in SQL Server 2005 Books Online.

WORKAROUND

To work around this problem, do any of the following:

If current scripts are available to add logons, users, and permissions, drop and recreate them from scripts. For examples of using scripts to transfer logins between servers, see the following Microsoft Knowledge Base article:
246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
240872 How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
You can use the sp_change_users_login stored procedure to reassociate relationships between the syslogins, sysusers and sysalternates tables. However, the procedure makes best estimates on links, and may allow a user more access privileges than intended. Running the procedure with the Report option first will generate a list of users that will be altered. Afterwards, you should check to ensure that the affected users have the appropriate permissions. Also, be aware that the sp_change_users_login procedure does not fix permission problems derived from logons and users created in a different order on the database where the backup is restored.
Restore a dump of the master database from the time of the user database dump to the server before loading the user database. Doing this ensures that all user information in the user database matches correctly with the syslogins table in master.

WARNING: The master database contains server-wide information, and affects all databases on the server. By restoring the master database, you may encounter additional user IDs and/or databases that are lost or have incorrect permissions. Any changes to the master that have occurred since the time of the backup will be lost. Only use this method if you are certain that the backup version of the master database contains accurate information for the user database in question and all other databases on the server.
Use Transfer Manager (for 6.x) or DTS (for 7.0) to copy the logins. Be aware that the passwords will not be transfered using this method.
Contact your primary support provider.

Keywords: kbprb kbusage KB168001