This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(240, 64%, 33%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAJ%3C/text%3E%3C/svg%3E)
We would like to set up the Log shipping but we don't want the security details to be copied from the primary database to the secondary database. Is there any way to do that?
Appreciate any help.
What do you mean by "security details" ? Do you mean that you dont want to transfer logins from primary to secondary database server ?
Mainly, the users list under Security .
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 30%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETH%3C/text%3E%3C/svg%3E)
I guess it depends on what you exactly mean by "system tables". If you mean "system tables in the system databases", then that isn't replicated. If you mean "system tables inside the log shipped database" (like the tables behind sys.tables and sys.indexes), then that is replicated. (I know you know this, just wanted to clarify for other readers... :-) .)
The logins aren't in the database so that part is already taken care of for you. But do see Shashank's reply, we need more details...
In Log Shipping process, during initial the back up of database, we don't want the users list of the primary database to be backed up; so that during the restoration it wont be available in the secondary database in the secondary db.
Hi @Anitha John ,
We would like to set up the Log shipping but we don't want the security details to be copied from the primary database to the secondary database. Is there any way to do that?
No. Because the essence of log shipping is restoring the backup files of primary dbs in the secondary instance. And all changes to the system tables are logged operations,security changes are propagated through to the secondary server (or servers) automatically.
You can try to make some changes in the secondary instances after log shipping.
More information: how-to-configure-security-for-sql-server-log-shipping
BR,
Mia
If the answer is helpful, please click "Accept Answer" and upvote it.
Because all changes to the system tables are logged operations, security-related changes are propagated through to the secondary server (or servers) automatically
Why would that be, LS is moving transaction log for a user database, changes to system tables would not be replicated.
Hi @Anitha John ,
Is the reply helpful?
BR,
Mia
If the answer is helpful, please click "Accept Answer" and upvote it.
but we don't want the security details to be copied from the primary database to the secondary database. Is there any way to do that?
Mainly, the users list under Security .
No, this will be transferred in logshipping because this is part of the database and you can do nothing to stop it. When you restore database all the users are transferred as they are part of the DB, but not the logins as they are at server level.
EDIT: From further comments
But in this case we should make sure that the users should not copied from the primary db to the secondary db.
This is not possible, user will be copied. The other thing is since you are not copying logins(assuming) these users will be orphaned users, the users without any login, and would not work. For a user to work a login has to be associated with it.
Thank you! But is there a way we can remove it from the secondary db once after restoration. Secondary db will be a stand by db.
Strange request, I dont know why you need this but once DB is restores in standby mode you cannot delete anything from it as it is read only. You would have to drop it on primary and eventually when logs reach secondary it will be dropped.
We have a such requirement. Our customer data is hosted in SQL DB servers in AWS. Many customers require a replica of the database on-prem for various reasons. So we were using MS DB Syn Framework, but there is severe performance issues we are facing and also this framework has been deprecated for the last few years, we have decided to go for alternative approach which is nothing but the log shipping. But in this case we should make sure that the users should not copied from the primary db to the secondary db.
@Anitha John I have edited my answer and have added few more information that might be helpful, related to your recent comment. Please check
Thank you @Shashank Singh
We know that those are orphaned users; still because of this we didn't get the approval from our security team.
By disabling the connect permission to user, do you mean we should do this on our primary db?
Sorry ignore my previous reply, since database is in read only/standby you cannot do anything. I forgot that point. I do not see any way to to achieve what you are asking.
ok.. thank you..
Can we change the stand by mode to read only and then remove the orphaned users and then move back to standby mode?
The mode is basically readonly\standby to make any changes to the DB you need to bring it in read write mode and moment you do that you loose LS. Unfortunately this is how it is. To setup again you have to restore it from backup of source database thereby again bringing the users to database which you do not want.
In other words: the copy is a binary (identical in every aspect) copy of the source database and it is impossible to change anything inside this database (without breaking your log shipping). So this is a dead end. You have to take a step back, look at the bigger picture, and see how you want to handle your situation.
You can create one user for logging and can use the same in Primary & secondary so that there will be only one login in Secondary. You can use a windows account for added security.
Just to understand ,why such a requirement ? what is the concern of having logins there?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(192, 23%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJW%3C/text%3E%3C/svg%3E)
As others have pointed out - you cannot modify the users in the database and it really isn't an issue. Unless your customers know the SID of the login - they won't be able to add a login and tie that login the user in the log-shipped database anyways.
If this is really an issue - you may want to reconsider how you provide that data to your customers. Another option would be to setup replication and only replicate the specific data they need. You can then determine what indexes (at least the clustered index and PK should be replicated) for the replicated tables.