Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cannot drop mapped windows login from SQL Server due to ENDPOINT permission Expand / Collapse
Author
Message
Posted Monday, April 22, 2013 2:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:59 AM
Points: 112, Visits: 92
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?
Post #1445160
Posted Monday, April 22, 2013 2:44 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 1,304, Visits: 1,134
Hi,

If the endpoint is no longer required (and you are sure), have you tried dropping it?

DROP ENDPOINT <endpoint name>

Andrew
Post #1445186
Posted Tuesday, April 23, 2013 6:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:59 AM
Points: 112, Visits: 92
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!
Post #1445392
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse