Sql-server – User mapping to a standby database

log-shippingloginssql serversql-server-2012standby

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?

Best Answer

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.