March 18, 2014 at 12:29 pm
One of our DBAs recently left, and when I was removing there login I found they had explicitly granted a connect on the availability group's endpoint to it's login.
The login itself is a sysadmin, so everything I can find online seems to say that it should automatically have connect permissions to all endpoints.
Is there any reason that this would need an explicate grant? And if not, will just revoking the permission cause any issues? I am afraid that just revoking the permission will bring down the availabity group even though I cannot find a reason it exists.
Thanks,
Jamie
March 18, 2014 at 2:41 pm
Are you sure permissions on the endpoint were not granted to a service account on the partner box?
March 19, 2014 at 5:43 am
Both the primary and the secondary have the same grant. It is on the service account running the SQL Server service.
March 19, 2014 at 7:30 am
It's probably the result of using the same grant script on both nodes of the cluster. Since the account is already a sysadmin, I would not worry too much over the "extra" grant, unless you have auditors nosing around.
March 20, 2014 at 5:39 am
What are you using to ascertain that the login has a grant on the endpoint?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 20, 2014 at 6:18 am
I originally wound it by checking to see why I could not drop the login. I checked for any other using sys.server_permissions.
Select pm.class_desc,
pm.state_desc,
p2.name,
p2.state_desc,
p.name AS Grantee,
p3.name AS Grantor
from sys.server_permissions pm
join sys.server_principals p on pm.grantee_principal_id=p.principal_id
join sys.endpoints p2 on pm.major_id=p2.endpoint_id
join sys.server_principals p3 on pm.grantor_principal_id=p3.principal_id
where class_desc = 'ENDPOINT'
I also, at some point, checked it in the UI.
March 20, 2014 at 8:54 am
pheonix623 (3/20/2014)
I originally wound it by checking to see why I could not drop the login. I checked for any other using sys.server_permissions.Select pm.class_desc,
pm.state_desc,
p2.name,
p2.state_desc,
p.name AS Grantee,
p3.name AS Grantor
from sys.server_permissions pm
join sys.server_principals p on pm.grantee_principal_id=p.principal_id
join sys.endpoints p2 on pm.major_id=p2.endpoint_id
join sys.server_principals p3 on pm.grantor_principal_id=p3.principal_id
where class_desc = 'ENDPOINT'
I also, at some point, checked it in the UI.
which account is the grantor for the user in question?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 20, 2014 at 11:36 am
I'm not sure about this question. The grantor is the user login I want to drop; the grantee is the Service Account for the SQL server instance.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply