3

When a full-backup is restored by the DBA, does the DBA need to restore permissions, users, and roles separately? Or does it all come with the full restore?

I am from the dev team. We are migrating from SQL Server 2008 to 2014. We asked the DBA to restore a full backup.

They have restored the full backup to the new server, but I can see discrepancies in users, permissions, and roles between the old and new servers.

Why it is happening?

5

When you move a database from one server to another via backup /restore, the users within the database stay intact .However, the logins associated with the users might not exist in the destination server.

For this to work as expected, you will need to move the associated logins and then check for any orphan users.

  1. Use this link to get the script for transferring the logins. You can verify the logins with the users in the database and move only those:

    How to transfer logins and passwords between instances of SQL Server (Microsoft Support)

  2. Once the logins associated with the particular databases are moved, check for any orphan users. This blog will help you understand more about this:

    Understanding and dealing with orphaned users in a SQL Server database by Atif Shehzad

EXEC sp_change_users_login 'Report'

EXEC sp_change_users_login 'Auto_Fix', 'user'
1

David Browne - Microsoft:

Database users, roles, and permissions are stored in the database, and will be restored. Server logins are not.

See Troubleshoot Orphaned Users (SQL Server)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.