Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
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)
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'
Database users, roles, and permissions are stored in the database, and will be restored. Server logins are not.
