User logons and permissions on a database may be incorrect after the database is restored
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
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.
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.
- 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 Server240872 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.
Article ID: 168001 - Last Review: 03/28/2006 05:14:56 - Revision: 5.3
- kbprb kbusage KB168001