Restoring db's onto other servers and users … | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Restoring db’s onto other servers and users …

Have u ever experienced the issue of having to restore a "production" backup database on a "test" server and finding that all your security/users policy screw you up? Usually we dont have the same security policies on such a different servers, do we? Yes, although you have the same uid’s on both servers (such a nice coincidence, Murphy says), this stupid DBA has to rebuild the whole security (delete users, roles, etc) and set it up again, as for the developers to use it and get rid of "production" users … is there any other alternative I am missing?
Are you using Windows Authentication or SQL Server Authentication? If it’s the former you shouldn’t have to do anything. If it’s the latter you will always need to fix the users using sp_change_users_login, but that’ll fix the IDs and you shouldn’t have to do anything to the permissions. Tom Pullen
DBA, Oxfam GB
Mixed mode is what we are using …… thus, yes, I am doing a bit of everything with both types of users …

For SQL Server users you will need to fix them. For Windows accounts you shouldn’t have to do anything provided the logins already exist on the test server. Tom Pullen
DBA, Oxfam GB
Handful articles to handle login migration for future: Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.