Q. How to create users on secondary standby  database in Log shipping  ?

Answer:  You can create the user on the primary and the log shipping will take it over to the secondary. Make sure if you’re also creating a login in Secondary Server that you explicitly specify the SID.

You can create a user without a login in SQL Server 2005 and newer. However, you can see these steps to simplify it for you:

1. Create login on production server
2. Create user in database mapped to login
3. Drop login on production server
4. Grant any required permissions to user in database
5. Create login on log shipping standby server
– a. If a SQL authenticated login, create login while specifying SID from production machine
– b. If Windows auth. login, create login without specifying SID
6. Make sure log shipping jobs have completed one full run since completion of step 4

Note: (Reason to follow above additional steps than regular process)

Existing users can query the log shipping secondary.
You cannot create new users in the secondary because that’s a change and the database is read-only (no changes permitted)

 

Reference: http://www.sqlservercentral.com/Forums/Topic1047578-391-1.aspx

Advertisements