1

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?

|improve this question
  • 1
    Where did you go to add the domain group? It sounds like you added it to the active cluster node which is no longer the active node. – Fred Shope Apr 12 '16 at 12:42
  • @GaryMazzone,How user are try to logged. Through remotely or locally to SQL Server. – Md Haidar Ali Khan Apr 12 '16 at 13:05
  • If remote they get the proper rights. Locally the get no rights – Gary Mazzone Apr 12 '16 at 14:57
1

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 sysadmin role 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 master and msdb system 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.

|improve this answer
  • This is not an AG setup. It is a cluster. The master and MSDB database are on the shared disks and no matter what I do the user issue stays the same. If I login on the active windows server the user gets no rights. If I log in from the inactive server the user gets sysadmin. If I shift the servers (manual failover) the process changes. Were the user had full rights he now has none and where they had no rights they are now sysadmin – Gary Mazzone Apr 12 '16 at 14:57
  • Ah, so you're saying if you log into Windows on the node that currently owns your FCI (regardless of which node is the owner), the users do not have expected SQL permissions. But when the user logs into Windows on the non-owner node, they have SQL sysadmin as expected? – AMtwo Apr 12 '16 at 15:44
  • Yes. and they get normal rights if logged in from their local machine – Gary Mazzone Apr 12 '16 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.