locked
Drop a user does not work ! RRS feed

  • Question

  • Hi Guy's,

    I am trying to drop a Login but I am facing the followin error:

    "Login has granted one or more permission revoke the permissions before drop the user"

    I issue the following query and it result:

    Select * from sys.server_permissions
    where grantor_principal_id =
    (Select principal_id from sys.server_principals where name = N'Test1') ;

    105 ENDPOINT 65539 0 266 263 CO   CONNECT G GRANT
    105 ENDPOINT 65539 0 275 263 CO   CONNECT G GRANT

    Any idea how can it revoe these permissions and drop the login ?

    Thanks

    Monday, August 27, 2012 12:14 AM

Answers

  • Try

    USE master; REVOKE GRANT OPTION FOR CONNECT SQL FROM Test1;

    REVOKE CONNECT ON HTTP ENDPOINT::nameofendpoint TO [DOMAIN\USER] 

    sys.endpointsAll endpoints and all generic properties
    sys.database_mirroring_endpointsThe Database Mirroring endpoints
    sys.service_broker_endpointsThe Service Broker endpoints
    sys.soap_endpointsHTTP endpoints that carry a SOAP-type payload
    sys.endpoint_webmethodsSOAP methods defined on endpoints
    sys.tcp_endpointsAll TCP endpoints and properties
    sys.http_endpointsAll http endpoints and HTTP properties
    sys.via_endpointsAll VIA endpoints and properties


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Monday, August 27, 2012 3:51 AM

All replies

  • Try

    USE master; REVOKE GRANT OPTION FOR CONNECT SQL FROM Test1;

    REVOKE CONNECT ON HTTP ENDPOINT::nameofendpoint TO [DOMAIN\USER] 

    sys.endpointsAll endpoints and all generic properties
    sys.database_mirroring_endpointsThe Database Mirroring endpoints
    sys.service_broker_endpointsThe Service Broker endpoints
    sys.soap_endpointsHTTP endpoints that carry a SOAP-type payload
    sys.endpoint_webmethodsSOAP methods defined on endpoints
    sys.tcp_endpointsAll TCP endpoints and properties
    sys.http_endpointsAll http endpoints and HTTP properties
    sys.via_endpointsAll VIA endpoints and properties


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Monday, August 27, 2012 3:51 AM
  • Thanks for the reply but how can I find the name of the ENDPOINT ?
    Monday, August 27, 2012 4:49 AM
  • Query the system tables I posted above.

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Monday, August 27, 2012 5:23 AM
  • select * from sys.endpoints

    Ramesh Babu Vavilla MCTS,MSBI

    Monday, August 27, 2012 5:33 AM