Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a production Server say ServerA I have setup log shipping to ServerB which is left in read-only mode. The purpose of this log shipping is to lower the load on production server for some expensive queries (painful reports).

Now if I have to create some logins using our domain accounts. I cannot do this because the secondary database is in standby mode.

I thought if I create these logins on Primary server it will be copied over to secondary server then the logs are restored there but this isnt the case.

I have done a lot of research online finding a way around to this. I found the following resources for this. I tried every method suggested in this articles but none of them seems to work.

1) Log Shipping in SQL Server 2008 R2 for set BI on replicated database

2) How to transfer logins and passwords between instances of SQL Server

3) Orphaned Users with Database Mirroring and Log Shipping

Has someone experienced the same issue? what did you do? Is there any way around for this issue? Any suggestions any pointer please.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Two options:

  • On your log shipping primary server, find a user in that database with the permissions you want (or create one). If you create one, log shipping will then transfer that over (since that's a database-level object). Then, to find the SID of the user you want on your primary server: select sid from sys.database_principals dp where type = 'S' and name = '<<YourUserNameHere>>'. (SID is basically a unique ID - it's completely unreadable) Then, on the secondary server, create a login with that specified SID: CREATE LOGIN [<YourLoginNameHere>>] WITH PASSWORD ='<<YourPasswordHere>>', SID = <<the SID from primary server>>. By specifying the SID, that login will have the same SID as the user in your logshipped database - you should then be able to log into that user.
  • Create a login with server-level permissions (i.e. sysadmin) on your log shipping destination. This is a really bad idea for a multitude of reasons (especially if you have other databases on that server).

Worked example of creating a user: I have a database called LogShippingTest and I want to give a login on my secondary server db_datareader on the logshipped copy so that Marketing can read stuff.

On the primary server:

USE [LogShippingTest];
CREATE USER [DataReaderForMarketing] WITHOUT LOGIN;
ALTER ROLE [db_datareader] ADD MEMBER [DataReaderForMarketing];
SELECT sid FROM sys.database_principals dp WHERE type = 'S' AND name = 'DataReaderForMarketing'

Let's say the SID is 0x70DD0D59ADF3FD4ABB000037826A2888 (I just made that up but we'll go from there). Now, either force log shipping to run, or wait until it runs itself and the user exists on the secondary side. Then, on the secondary:

USE [master];
CREATE LOGIN [DataReaderForMarketing] WITH PASSWORD 'ThisIsAVerySecurePassword', SID = 0x70DD0D59ADF3FD4ABB000037826A2888 

You should then be able to log in as the DataReaderForMarketing login on the secondary server and have db_datareader on the logshipped secondary database. (You can change the permissions on the primary database user and they'll logship down to apply to the secondary, too!)

share|improve this answer
    
Thank you very much Simon, I followed you 1st suggested method and it worked. I was creating users on master without the SID option and it resulted in two different SIDs for a single login and thats why I was having problems. –  M.Ali Mar 22 at 22:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.