Friday, April 13, 2012

Drop User Failed - User has granted one or more permission(s).

Today I was tasked with cleaning up our security logins, and one of the users came up with the following error:


I wasn't quite sure what to do. After a little google-fu, I found the following script:

select * from sys.server_permissions
where grantor_principal_id =
(select principal_id from sys.server_principals where name = 'domain\user')

And got two results:
This told me the user owned an endpoint, but which one? The previous query only gave me the major_id which was 65536.


So I ran SELECT * FROM sys.endpoints and got the following results:
I saw endpoint_ID 65536 and found it was a mirroring endpoint. A mirroring remnant of mirroring that was once set up, but was now gone. So all I had to do was drop that endpoint with:
DROP ENDPOINT Mirroring

And I was good to drop the user.

No comments:

Post a Comment