PRB: User Logon and/or Permission Errors After Restoring Dump

• Microsoft SQL Server versions 6.0, 6.5

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 the 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 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




• 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 error 4002 "Login failed" while attempting to log on to the server. If the user logons do exist, but the SUID values in master..syslogins and the sysusers table in the user database differ, the users may have different permissions than expected in the user database.

WORKAROUND

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

• If current scripts are available to add logons, users, and permissions, drop and re-create them from scripts.



• In SQL Server 6.5, you can use the sp_change_users_login stored procedure re-associate 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.



• Contact your primary support provider.

Keywords          : kbusage SSrvAdmin 
Version           : 6.0 6.5
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: April 9, 1999