CODEWITHSUNDEEP
sql-servercluster-computingsql-server-2016readonlyfailover
D34th
D34th

Reputation: 331

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.

Upvotes: 2

Views: 1345

Answers (1)

S3S
S3S

Reputation: 25152

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)

Upvotes: 1

Related Questions