SQL Server Performance Forum – Threads Archive

keep test server users as such after restoration

I have a production server and test server.Both have different users.I need to refresh the test server with the data from the production server and keeps the users in the test servers as such before the restoration.Due to some constraints,i can only use the backup file from the production server to be used for resotration.In this scenario how can i keep the test server’s users as such after the resotration of the latest production data beeing restored. THIRUVENGADAM

the restore not change the users. You dont have a problema if you restore the data. (( sorry but my english is not god )) A hope can i help you. Bye
The Pipo Dba.

Use DTS instead to export tables & use generate script for SPs and triggers etc. or use a script to retain logins on test server when restored a backup from production. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Restore the database from the production server to the test server. If you restore the database the users in the production database will also be restored. But they dont have any logins in the test environment. So,Use ‘sp_validatelogins’ to validate the users logins. Remove the orphaned users with the help of this stored procedure. N.Babu Godson Samuel

Hi THIRUVENGADAM, I do sth similar once in a while. Though the file paths are different at least my usernames are mostly the same. I started making simple scripts for use after restore to maintain logical file names and database access. Main purpose for this ist to simplify and speed up my work, to get better quality (a script will never forget sth) and to have a chance to call a colleague and ask him to do a job for me. Second thing is that I want to get a bit more into T-SQL. I am on a different computer right now so I cannot post an example. As mentioned my scripts are very simple (I don’t know yet how to do sth like ‘if username xxx does not exist then sp_addlogin xxx … ‘). I guess your test server already knows the test user names, so you don’t need sp_addlogin (use sp_grantlogin for Windows authentication) to create them. After the transfer you will also have some zombie ids in your database. (try sp_change_users_login ‘report’) You can get rid of them using sp_change_users_login and afterwards remove the id from the db. If I understood your purpose right then some lines like this one should help you already: <pre id="code">use MyTestDatabase exec sp_grantdbaccess @loginame = ‘testuser1′ exec sp_grantdbaccess @loginame = ‘testuser2′ … </pre id="code"> Hope I got you right. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

]]>