Cadzow Knowledgebase


Welcome
Contact Us
Professional
Services

Consulting
Knowledgebase/
Site Search

Remote Support

Print Friendly

Problems with SQL Users after restoring SQL Server database

Problem

If you backup a SQL Server database accessed via SQL Server users (as opposed to Windows NT/domain users), then restore that database on another server, recreate the SQL Server users and attempt to apply permissions, you receive errors.

This occurs because the SQL user accounts are brought across from the original server but are not automatically linked to the SQL user accounts on the new server, even if they have the same name.

Solution

Remap the SQL user accounts as listed under the database with the user accounts as listed under Security / Logins:

  1. Open Query Analyser.

  2. Run:

    sp_change_users_login 'report'

    This shows the orphaned accounts.

  3. Then run:

    sp_change_users_login 'update_one', 'user', 'user'

    where user is the name of the orphaned user.

See Also

Copyright © 1996-2018 Cadzow TECH Pty. Ltd. All rights reserved.
Information and prices contained in this website may change without notice. Terms of use.


Question/comment about this page? Please email webguru@cadzow.com.au