Post reply

Unable to remove orphaned user

  • August 7, 2018 at 11:38 am

    #375480

    I'm trying to clean up a SQL server.  There is one orphaned user that I cannot seem to get rid of.  When I try to delete the use I recieve the following error:
    "The database principal has granted or denied permissions to objects in the database and cannot be dropped."
    When I run the following query it looks like they are tied to symmetric keys and certificate.
    select *
    from sys.database_permissions
    where grantor_principal_id = user_id ('sql_user');

    class_desc
    SYMMETRIC_KEYS
    CERTIFICATE

  • August 7, 2018 at 12:11 pm

    #2000686

    jon.wilson - Tuesday, August 7, 2018 11:38 AM

    I'm trying to clean up a SQL server.  There is one orphaned user that I cannot seem to get rid of.  When I try to delete the use I recieve the following error:
    "The database principal has granted or denied permissions to objects in the database and cannot be dropped."
    When I run the following query it looks like they are tied to symmetric keys and certificate.
    select *
    from sys.database_permissions
    where grantor_principal_id = user_id ('sql_user');

    class_desc
    SYMMETRIC_KEYS
    CERTIFICATE

    Revoke the permissions that sql_user granted, add them back when logged in as a sysadmin and then delete sql_user.

    Sue

  • August 7, 2018 at 12:41 pm

    #2000693

    Works, thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply