Does Log Shipping copy over logins and users to the secondary server?

Question

Does Log Shipping copy over logins and users to the secondary server?

brettwalker42

SSC Enthusiast

Points: 178
More actions
February 19, 2020 at 10:02 pm

#3727216

Hello,
I'm having a problem with log shipping in that the logins and users are not being copied over to the secondary server. I've got 10 logins/users on the primary and I want the same 10 logins and users to be created/updated on the secondary server with the same databases and permissions. The users need to be in a different database than the primary. Log shipping is running and working as expected.
On the primary, I am log shipping a database named DB1 to a database named DB2 on a different server. The users exist in both places (primary and secondary) but the permissions on the secondary are incorrect. I can't change permissions on the secondary because it is in "Standby / Read-Only" mode. Can log shipping update the logins/users on the secondary database or do I need to delete all the logins on the secondary and let log shipping copy them over?
What do I need to do to get the logins and users on the secondary to match the logins and users on the primary? The only caveat is that the database name on the secondary is going to be different than the database name on the primary.
Both database servers are running SQL Server 2014.
Any help/advice/suggestions are welcome.
Thanks in advance,
Brett

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Logins are created at the server level - and users are added at the database. To have the logins on the secondary server tie to the users in the database on the secondary, those logins must be created with the same SID as the logins on the primary.

You can manually build a script to generate the create login - or you can use a tool like this: https://docs.dbatools.io/

The older method is to install sp_help_revlogin - and then manually add any server level privileges (if needed).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

brettwalker42 SSC Enthusiast Points: 178 More actions · Answer 2

Hi Jeffrey,

Thanks for the response. I understand what you're saying but I have 3 questions:

How do I delete the logins and users on the secondary server since it's in Standby and read-only? I presume I need to do that right?
Assuming I do get the logins on the secondary deleted, I will need to run the create login script I created on the secondary server right? Again, since it's read-only, how do I do this? Can I take it out of read-only temporarily to do this? If so, will this interrupt users on the secondary server?
Since I'm deleting the logins on the secondary server (I assume), I assume I'll need to recreate the users on secondary - right? I should be able to add the create users in the create login script right?

I appreciate it,

Brett

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

brettwalker42 wrote:

Hi Jeffrey,
Thanks for the response. I understand what you're saying but I have 3 questions:

<li style="list-style-type: none;">
How do I delete the logins and users on the secondary server since it's in Standby and read-only? I presume I need to do that right?

<li style="list-style-type: none;">
Assuming I do get the logins on the secondary deleted, I will need to run the create login script I created on the secondary server right? Again, since it's read-only, how do I do this? Can I take it out of read-only temporarily to do this? If so, will this interrupt users on the secondary server?

<li style="list-style-type: none;">
Since I'm deleting the logins on the secondary server (I assume), I assume I'll need to recreate the users on secondary - right? I should be able to add the create users in the create login script right?

I appreciate it,
Brett

The SERVER is not read only, the DATABASE is read only. Like Jeffery said, the logins are at the server level. You can drop or recreate these as needed.

As far as the database, taking it out of read-only may (I think!) break the log shipping.

Here is my question. Do you want the certain users to only be able to access the secondary read only databases, and not the primary? And, do you want certain logins to only access the read/write primary?

If so, here is a suggestion

Create both set of logins on the primary server, and the users in the primary database(s).
Create both sets of logins on secondary server, the users will be created automatically in the secondary databases(s)
Disable the read-only logins on the primary
Disable the read-write logins on the secondary.

That will keep the logins from accessing the wrong server. and will make sure that all logins are present on both servers so in the event of a failure of one of the nodes, you only have to enable the disabled logins.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

You don't delete the users on the secondary - users are part of the database and since that database is not accessible you cannot make any changes to the users. If you switch the database to standby - so it can be accessed as read-only and the login created on the secondary matches the user based on SID - then the user would have whatever permissions were granted in the database on the primary.

The log shipped database is updated when you apply the transaction logs. When you create a new user in that database - the new user will be added to the log shipped database when the transaction log that contains that transaction is applied to the database on the secondary. Once that has occurred, if the login on the secondary server matches by SID to the user created in the database - then that login could access the secondary database once it has been switched to standby mode. However, since the database is set to read-only when in standby mode - that login/user will only have read-only access to the database.

If you need to create users in the secondary that do not have access to the primary - but will have access to the secondary, then you need to create that user on the primary database first. There are several approaches:

Create the login (not the user) on the secondary and get the SID from that login. Then, create the user in the secondary database without a login but with the SID identified from the login on the secondary. Now, when that new user is transferred across to the secondary database it will tie to the login on the secondary.
Create the login and the user on the primary - script out the login with the SID from the primary and create that login on the secondary. Disable the login on the primary (or delete it). When the new user is transferred to the secondary database it will tie to the login created on the secondary.

Either way - the key here is that both the login and the user in the database share the same SID. SQL Server utilizes the SID and not the external name.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Michael L John One Orange Chip Points: 27055 More actions · Answer 5

One addition to Jeffery's post. The SIDS only matter for SQL logins. Windows logins use the active directory SIDs.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/