Archive | January 2014

Drop failed for login | Endpoints

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’)

Sample result:

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!

Advertisements