SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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!

Nebraska SQL from @DBA_ANDY

I’m a forty-something Microsoft SQL Server DBA of 15+ years, a devoted husband, and a father of three young boys. I have been a DBA at a public university, at a major bank, at a healthcare system, and I now work as a remote DBA with customers across the United States. I write and speak primarily about the tips and tricks that I discover along my SQL Server journey.

Comments

Leave a comment on the original post [nebraskasql.blogspot.com, opens in a new window]

Loading comments...