Search code examples
sql-servercluster-computingsql-server-2016readonlyfailover

SQL Server 2016 Read Only Replica With Different Database Security


Is it possible to have a SQL 2016 read only replica that is not used as a fail over server with different database security than the primary?

I want to ensure that the users don't have access to the primary node and only have read-only access.


Solution

  • Is it possible to have a SQL 2016 read only replica that is not used as a fail over server

    Yes. But you need to elaborate. For example, if it is in asynchronous mode, you can only do manual failovers. This is sort of like not using it as a fail over server, since you would have to physically force it. See failover and failover modes.

    Is it possible to have a SQL 2016 read only replica with different database security than the primary

    Yes. In fact, one of the annoying parts of AlwaysOn AG's is syncing logons. Most people want them to match. That's why many people have blogged ways on how to do this, and others have created tools in powershell to do this, as well as sync agent jobs, alerts, etc.

    I want to ensure that the users don't have access to the primary node

    Since replicas sit on different servers as the primary, you can keep the permissions separate across the instances easily. Just give your users a logon to the replica instance and database, and not the primary instance and database. Remember there is a difference in the server logon and the user mapping. For your RoR, your user will need a server logon and then have their userid mapped to your replica.

    I want to ensure that the users only have read-only access.

    Since it is already read-only, that's the only access they would have. They wouldn't be able to UPDATE / DELETE / INSERT even if they were sysadmins (unless, of course they changed it our of read-only)