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.
problem may reveal itself in several ways:
- While logging on to a 6.x server, users may receive the
Msg 4002, Level 14, State 1, Server
Microsoft SQL Server, Line 0
- While logging on to a 7.0 server, users may receive the
Msg 18456, Level 14, State 1,
failed for user '%ls'.
- While trying to access objects within the database, users
may receive the following error:
Msg 229, Level 14, State
%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
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.
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
. For more information about compatibility views, see the "Compatibility Views (Transact-SQL)" topic in SQL Server 2005 Books Online.
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
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
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
- 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.
Article ID: 168001 - Last Review: March 28, 2006 - Revision: 5.3
- 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