I have created a 2012 WFC and SQL Server 2014 is clustered on it. The cluster works as expected but have a question on Users:
I added a windows domain group to the cluster as sysadmin.
When the user tries to log into SQL on the active server the users rights are not right. They can’t see anything. If the same user logs in from the no active server they have full admin rights, if they log in from their local machine they have the proper sysadmin rights.
Anyone have a clue as to what might be happening?
Best Answer
From a terminology perspective, there is a Windows Server Failover Cluster (WSFC), and a SQL Server Failover Cluster Instances (FCI). There's also an Availability Group (AG). AGs and FCIs are built on top of a WSFC. AGs and FCIs can be combined or separate. I try to avoid saying "Cluster" and use these acronyms to avoid ambiguity.
From your description, it sounds like you have an AG. An AG is a database-level of HA/DR. Anything and everything within the database is kept in sync between the replicas on your AG. For a visual, look at things in Management Studio, and use Object Explorer to drill-down to see what is contained within the database, and what is outside the database.
The
sysadminrole is a server role, so therefore that permission is not kept in sync between replicas. Additionally, the server login is also outside the database, and not automatically synced. You will need to create logins and grant server-level permissions separately on every instance that is part of your AG.Logins, server-level permissions, linked servers, SQL Server Agent Jobs, backup history, (among other things) are all stored in the
masterandmsdbsystem databases, which are not part of the AG, and therefore not kept in sync between instances. You'll need to keep this in mind when you're building a solution using Availability Groups.