April 22, 2013 at 2:15 pm
Trying to do some housekeeping of our SQL logins and I noticed that my active directory account has a mapped login on our SQL server. I am also granted 'sysadmin' access to the SQL server by way of a mapped group also so I would like to drop my individual login. I am having trouble dropping it and am getting the following error:
Msg 15173, Level 16, State 1, Line 2
Login 'abc\abc123' has granted one or more permission(s). Revoke the permission(s) before dropping the login.
After doing some digging around on the web and some looking around the server I ran these queries (my login's principal id is 306)
Select * from sys.server_permissions
where grantor_principal_id = (Select principal_id from sys.server_principals where name = N'abc\abc123')
SELECT * FROM sys.[database_mirroring_endpoints]
Results:
class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc
----- ------------------------------------------------------------ ----------- ----------- -------------------- -------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- ----- ------------------------------------------------------------
105 ENDPOINT 65536 0 307 306 CO CONNECT G GRANT
name endpoint_id principal_id protocol protocol_desc type type_desc state state_desc is_admin_endpoint role role_desc is_encryption_enabled connection_auth connection_auth_desc certificate_id encryption_algorithm encryption_algorithm_desc
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------ -------- ------------------------------------------------------------ ---- ------------------------------------------------------------ ----- ------------------------------------------------------------ ----------------- ---- ------------------------------------------------------------ --------------------- --------------- ------------------------------------------------------------ -------------- -------------------- ------------------------------------------------------------
Mirroring 65536 306 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 1 PARTNER 1 3 NEGOTIATE 0 1 RC4
We are not using Database Mirroring on this server although it might a remnant of us testing out stuff before it went into production.
I attempted to revoke the permissions by executing this query:
REVOKE CONNECT ON ENDPOINT::Mirroring FROM [abc\abc123]
Results:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
I also tried to run this as another login that has 'sysadmin' to no avail. Any ideas? Am I on the right track?
April 22, 2013 at 2:44 pm
Hi,
If the endpoint is no longer required (and you are sure), have you tried dropping it?
DROP ENDPOINT <endpoint name>
Andrew
April 23, 2013 at 6:46 am
After dropping the endpoint I was then able to remove the mapped login. I was so focused on those errors I didn't even think to go drop the endpoint.
Thanks Andrew!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply