Revoke the permission(s) before dropping the server principal

, 2018-06-27 (first published: )

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!

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads