| Downloads |
|---|
| 23805.zip |
I often need to set up a test server so that the security matches that of the server I'm testing. To set up the test server, I usually stop the source server, then copy the system databases to the test server. However, if you can't stop the source server, you must script the security setup from the source server, then run it on the test server. Brian Knight provided a helpful script that did this in his article "Linked Servers" (August 2000, InstantDoc ID 8992).
However, if you have to recreate integrated logins created from OS groups and user accounts native to the source SQL Server, you use the Addusers utility (from the Microsoft Windows 2000 Resource Kit or the Microsoft Windows NT Server 4.0 Resource Kit) to dump the OS groups and logins for the source server to a text file. Then, you edit the text file and use the Addusers utility again to load the necessary groups and users into the OS of the test server. When you dump the user accounts from the target server with Addusers, you might want to use a character other than a comma as a delimiter with Windows XP and Win2K because some default OS accounts that Microsoft included for debugging have commas in their descriptions.
Listing 2 shows a script that recreates logins for domain accounts from a source server. Running this script on the source server produces two sets of scripts. The first set is similar to the script that Knight provided in "Linked Servers." The second script uses the source server's security setup to make the server name for SQL Server logins a variable.
Run both output scripts on your target server to recreate the security setup from the source server. This method works on XP (beta), Win2K, and NT OSs running SQL Server 2000 or 7.0.
