Sql-server – SQL Server grant permissions to STANDBY database

active-directorySecuritysql serversql-server-2012

So, first: the setup. We have SQL Server 2012 (ServerA) running in domain A. We have SQL Server 2012 (ServerB) running in domain B, separate subnet, no trusts. These are completely separate domains for security reasons and they cannot be joined/trusted/etc. We need to be able to query the data directly from domain B via Windows Authenticated logins. I was able to use this guide to set up transaction log shipping to get the databases from ServerA to ServerB (summary: create the transaction log shipping config, use WinSCP to copy the logs to the remote server, manually create the secondary using SQL script). So now we have the two databases running in STANDBY/read-only on ServerB.

Now, the problem: we cannot grant access to these databases because they are in read-only so we cannot modify the permissions. How can I grant read-only access to these databases (either at the server level or DB level) to a domain group from DomainB on ServerB? I've found several references to creating a SQL login on the sending side, but I can't find any way to replicate it with a Windows Auth Login.

Best Answer

Below steps should work :

  1. Create windows login on ServerA
  2. Create user in database mapped to login
  3. Drop login on ServerA - (OPTIONAL -if you want to have that login intact then leave it, else drop it).
  4. Grant any required permissions to user in database

  5. Create login on log shipping ServerB

    • a. If using SQL authentication then create login while specifying SID from ServerA

    • b. If using Windows authentication, create login without specifying SID

       ---To query for the SID on the ServerA:

  Select SID
  From sys.database_principals
  Where name = 'Test'

 ---To create the login on the ServerB :

  Create Login [Test]
  With SID = '<put SID from query here>',
  Password = '<add password here>'

  6. Make sure log shipping jobs have completed one full run since completion of step 4

Related Question