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.
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.
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 =
WHERE name = @GrantorName
Sure enough I found an offending row:
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:
ALTER AUTHORIZATION ON ENDPOINT::mirroring TO sa;
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.
Hope this helps!