The information in this article applies to:
- Microsoft SQL Server, versions 6.0 and 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.
|