Revoke the permission(s) before dropping the server principal

As a remote DBA I often have to log in to client systems for a fixed amount of time and then remove logins and other security after the work is done.

This morning while cleaning up I ended up with a new error for me:

--

Msg 15173, Level 16, State 1, Line 1
Server principal ‘NtiretyMirror’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.

--

Interesting.

At first I thought the error was that the login had *been* granted a permission, which seemed bizarre to me - every day I drop logins with all types of permissions and role memberships.

When I Google'd the base portion of the error - "Revoke the permission(s) before dropping the server principal" I found that I was incorrect, as described in an MSDB article here.

The problem wasn't that the login had permissions, but rather than it had granted someone *else* permissions.

https://i.imgflip.com/qygsn.jpg

The next question became how to tell what permissions we are talking about.  Since the permissions weren't on the NtiretyMirror login itself, how could I find out what permissions were involved?

There was a basic query in the MSDB post, but I tweaked it to give a cleaner resultset:

--

DECLARE @GrantorName nvarchar(4000)

SET @GrantorName = 'NtiretyMirror' /* Login in Question */

SELECT b.name as Grantor
, c.name as Grantee
, a.state_desc as PermissionState
, a.class_desc as PermissionClass
, a.type as PermissionType
, a.permission_name as PermissionName
, a.major_id as SecurableID 
FROM sys.server_permissions a
JOIN sys.server_principals b
ON a.grantor_principal_id = b.principal_id
JOIN sys.server_principals c
ON a.grantee_principal_id = c.principal_id
WHERE grantor_principal_id =
(
SELECT principal_id
FROM sys.server_principals
WHERE name = @GrantorName
)

--

Sure enough I found an offending row:

Grantor	Grantee	PermissionState	PermissionClass	PermissionType	PermissionName	SecurableID
NtiretyMirror	public	GRANT	ENDPOINT	CO	CONNECT	6

--

I looked in sys.endpoints and found that ID =6 is the mirroring endpoint.

Surprise, surprise - when I had set up mirroring (as NtiretyMirror - get it?) I had granted CONNECT to public as part of the process, and not the NtiretyMirror login owned that GRANT.

I could try to REVOKE the CONNECT, but did I really want to risk breaking mirroring?

I found the safer thing to do was to change the ownership of the Mirroring endpoint via ALTER AUTHORIZATION:

--

USE [master]
GO
ALTER AUTHORIZATION ON ENDPOINT::mirroring TO sa;
GO

--

Once I changed the ownership of the endpoint, my original query showed no permissions related to NtiretyMirror, and I was able to drop it successfully with the normal DROP LOGIN statement.

https://memegenerator.net/img/instances/66310140.jpg

Hope this helps!