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:
http://support.microsoft.com/default.aspx?kbid=246133
http://www.databasejournal.com/features/mssql/article.php/2228611
http://www.databasejournal.com/features/mssql/article.php/1438491 Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>