SQL Server Performance Forum – Threads Archive

Log shipping – permissions required

I have setup a nice log shipping solution on SQL 2000 Standard. Instead of using Linked servers (I couldn’t get them to work using NT authentication) I used DTS which has been working fine. However, the service account on the live server is a server administrator on the recovery server. What I would like to do is trim it’s rights down to the minimum needed. The problem I face is that if I just grant the user execute on the stored procedures it needs to run (stored in an admin database) I get an error from DTS saying that ‘Server user ‘DGASStratusServer’ is not a valid user in database ‘Snap” I can’t add the user to the datbase because it is in warm stand-by so the only option is to make the user server admin. Is there a way I can fix this perhaps? The server is used for other things and I dont want the account to be a server administrator.

First, BOL recommends building a SQL Server DTS package to transfer logins from the primary server to the secondary server and to resolve login SIDs across distinct servers. (The DTS Transfer Logins task, which you use to transfer the logins, is available only in SQL Server 2000 DTS.) You create and save the DTS package on the primary server, then set up the package execution by invoking dtsrun.exe through a SQL Server Agent job on the primary server. The package execution transfers the logins from one server to the other, but it doesn’t resolve their login SIDs. (I describe resolving logins in a later step.) However, to be able to resolve login IDs later, you must first create a file containing an export of the primary server’s syslogins table. To export the logins to the secondary server, the BOL article recommends that you create a two-stage SQL Server Agent job: bcp out and copy. In the first step, you export the logins to a file by using bcp in native mode. In the second step, you copy the logins to a file on the secondary server that you can use later for resolving logins during the role change. At that point you use the sp_resolve_logins stored procedure to resolve login SIDs on the secondary server. After you create the job, you can run it at regular intervals (e.g., nightly) to keep an up-to-date exported file of logins on the secondary server in case you need to make a log shipping role change. 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.

]]>