I have a standby database that is being restored in standby mode using
log-shipped transaction files downloaded from a vendor's sFTP site on
a daily basis.
I am required to give a read-only access on this database to someone
else, outside our domain.
With log shipping and the DB staying in standby mode, it stands there waiting for new transactions from the primary to commit. You cannot make changes to the secondary DB without breaking the transaction log chain and thus breaking the log shipping entirely as it's no longer an accurate replica.
To Change Secondary DB with Log Shipping in Standby Mode
The changes must occur on the primary DB first, and then those changes must be in a transaction log that is then applied to the secondary DB before the changes are effective on the secondary DB.
Steps to take
Essentially you should work with the vendor's MS SQL DBA which maintains the
SQL DB that maintains the process that sends the transaction logs to an SFTP mailbox to:
- Create a new SQL login on their SQL Server [primary] instance
- Grant the new login
fix database role in that DB (or explicit SELECT access to only the tables (or EXECUTE to procs, etc.) you need to give them access)
- Share the username and password with you
- Share the login SID value with you
Once you get all the needed information from the vendor and confirm the login is defined on the primary instance of Microsoft SQL Server, you can execute a CREATE LOGIN statement and pass that information in to create the login to match how it's defined on the primary server.
CREATE Login NewLogin WITH password = 'newpassword', SID = 0x90A672109A57D24585FEE4BF99E9BE19
NOTE: It is worth noting that if you create the login on the secondary instance with everything matching, the security changes will still not be effective until the next round of Transaction Logs you get via SFTP which have these changes in them from primary are actually committed to the secondary DB so timing is something to consider and test before publishing externally for their usage.
Getting the SID
Once a SQL login is created you can get the value of its SID by simply using
sp_helplogins and pass the SQL login name as an argument, and it will return the SID value for that principal.
EXEC sp_helplogins 'John';
sp_helplogins [ [ @LoginNamePattern = ] 'login' ]
The first report contains information about each login specified, as
shown in the following table.
Column Name: LoginName
Data Type: sysname
Description: Login name