2

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.

Now, I am required to give a read-only access on this database to someone else, outside our domain. Since it is on standby, I can not create a user and grant read access. I also asked the vendor if they can do this for me on primary database but they said no.

So I am asking here if there is any way round to solve this problem?

If I change the database to online status and created a mapped user to the database, would that be okay to bring it back to a standby again and continue the restoring process without hassle?

Improve this question
1

3 Answers 3

Reset to default
3

If I change the database to online status and created a mapped user to the database, would that be okay to bring it back to a standby again and continue the restoring process without hassle?

No, that will break log shipping.

Unfortunately, until SQL Server 2014 you don't have much for other options.

Grant access via stored procedures or views in another database. This will require enabling cross database ownership chaining, which can be a treacherous security road. And a lot of things could go wrong over time as tables are added, modified, and dropped.

Create the login on the log shipping primary instance with the associated database user, and disable the login on the log shipping primary instance. If you’re using SQL authentication, you may have to use a special script to transfer the SID to the log shipping secondary to get it all to work.

In 2014+, you can use CONNECT ANY DATABASE and SELECT ALL USER SECURABLES IN SQL SERVER to grant read only access.

Improve this answer
3

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.

Log Shipping

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:

  1. Create a new SQL login on their SQL Server [primary] instance
  2. Grant the new login db_datareader 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)
  3. Share the username and password with you
  4. 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.

Example T-SQL

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.

Example T-SQL

EXEC sp_helplogins 'John';

Syntax

sp_helplogins [ [ @LoginNamePattern = ] 'login' ]

Result Sets

The first report contains information about each login specified, as shown in the following table.

  • Column Name: LoginName

  • Data Type: sysname

  • Description: Login name

source

Further Resources

Improve this answer
2

You should create a login on your primary, create a corresponding user in log shipped database and wait until this user "flows" on your secondary, at this point you can drop the login on primary. If you use windows authentication, you've done. If you use SQL Server authentication, memorize the sid of the newly created login before you drop it, on your secondary you shoul recreate the login using this sid, the sintax is:

create login yourLogin
    with password = 'yourPassword',
    sid = 0xC10434A5BE32CE4E85EA71E6B04C068F -- the sid assigned to the login on primary
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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