none
Trouble dropping user in sql 2008 R2

    Question

  • Hi all.  I am trying to change the owner on a db and I get an error because the account is already mapped as a user.  I can't delete the account as a user because I get an error saying the database principal has granted or denied permissions to objects in the database and cannot be dropped.

    I run 

    select * from sys.database_permissions where grantor_principal_id = user_id ('USERNAME') and it returns 

    class   class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc
    17 SERVICE   65536 0 5 16 SN              SEND         G GRANT
    17 SERVICE   65537 0 5 16 SN              SEND          G GRANT

    I found something on here that says to use revoke send on service::fill_in_service_name to USERNAME  But my question here is how do I find the service name?

    Desperately need help revoking permissions for this user on the service.  Thanks

    Thursday, April 04, 2013 6:51 PM

Answers

  • Have you checked your service broker services? Take a look at the output of sys.services (from the context of that db).

    Hopefully the major_id output from above will correspond to one of the service_id column in sys.services.

    [EDIT: before you alter service broker settings make sure it won't break any applications using those services]


    ajmer dhariwal || eraofdata.com


    • Edited by Ajmer Dhariwal Thursday, April 04, 2013 9:34 PM
    • Marked as answer by radnorIT Friday, April 05, 2013 2:02 PM
    Thursday, April 04, 2013 9:16 PM
  • SELECT * FROM sys.services WHERE service_id IN (65536,65537)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by radnorIT Friday, April 05, 2013 2:02 PM
    Thursday, April 04, 2013 9:32 PM

All replies

  • Have you checked your service broker services? Take a look at the output of sys.services (from the context of that db).

    Hopefully the major_id output from above will correspond to one of the service_id column in sys.services.

    [EDIT: before you alter service broker settings make sure it won't break any applications using those services]


    ajmer dhariwal || eraofdata.com


    • Edited by Ajmer Dhariwal Thursday, April 04, 2013 9:34 PM
    • Marked as answer by radnorIT Friday, April 05, 2013 2:02 PM
    Thursday, April 04, 2013 9:16 PM
  • SELECT * FROM sys.services WHERE service_id IN (65536,65537)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by radnorIT Friday, April 05, 2013 2:02 PM
    Thursday, April 04, 2013 9:32 PM
  • Hi there,

    In the database security right click on dbo and see what user is it using for login.

    i guess it must be using the same account that you are trying to delete. If this is true change the database owner to some other user. I am sure you are able to delete this time.

    Thanks

    kumar

    Friday, April 05, 2013 12:43 AM
  • Thank you.  That gave me the service name and I was able to revoke the permissions.

    Much appreciated

    Friday, April 05, 2013 2:01 PM