Have you ever tried to drop a user from SQL in an effort to clean up your security and gotten the error: Server principal ” has granted one or more permissions(s).?
Was it related to SQL Server 2012 and your Availability Groups?
I ran into this recently and if it helps someone, then good!
First, make sure you know what the permission was for:
Select * from sys.server_permissions
where grantor_principal_id =
(Select principal_id from sys.server_principals where name = N’userNameHere’)
class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc
105 ENDPOINT 65536 0 283 282 CO CONNECT G GRANT
You should see an entry [or more than one] and the grantor would have the id of the user you were trying to remove — in this case it is for an endpoint. Now find out what that actual permission is for:
Select * from sys.server_principals where principal_id = 283 [replace with ID]
You can see that when creating the Availability Group the user creating the AG was assigned as giving permission to the HADR_Endpoint to the cluster account.
If you use the UI to change the permission it won’t work. You have to actually issue the t-sql:
ALTER Authorization on Endpoint::[Hadr_endpoint] to [OtherUser]
Now that this is done, you should be able to disable the existing user [or deny access] until you are certain it isn’t being used — and then feel free to delete it!
Questions? Comments? Post!
One thing that was strange to me is that with the advent of Availability Groups in SQL Server 2012 is that there isn’t a built in way to have SQL Agent Jobs run specifically for the active AG.
The workaround I have gone with is just adding a statement around the Job Step and then deploying the new version to both [all] nodes.
The script is very simple:
IF( select CASE
WHEN primary_replica = @@servername Then 1
from sys.dm_hadr_availability_group_states) = 1
–Here is where you would put the code for your job step
And that is it!
Deploy to all of your nodes and you are set moving forward no matter which node becomes the primary.
Questions? Comments? Please post!