locked
Dropping a database user that doesn't exist in AD RRS feed

  • Question

  • Team,

     

    I'm unable to drop a database user that is no longer in AD.

    This is a contractor account that left the company, and I don't have a login to associate with the database account.

    Is there a way to delete this account from my production database?

     

    Thanks in advance for the help.


    --- Best Regards, Igor Santos - DBA igor.santos@citrix.com
    Monday, June 13, 2011 4:35 PM

Answers

  • I had to revoke VIEW DEFINITION to the database role below so I could drop the user.

    The weird thing is that I could not find this VIEW DEFINITION permission on this role, on the sys.database_permissions table, which is something I was expecting to see.

     

    select * from sys.database_permissions
    where grantor_principal_id = user_id ('domain\user');

     

    USE [BAMPrimaryImport]
    GO

    REVOKE VIEW DEFINITION ON USER::[domain\user] TO [BAM_ManagementWS] AS [domain\user]
    GO

    DROP SCHEMA [domain\user]
    GO

    DROP USER [domain\user]
    GO
    --- Best Regards, Igor Santos - DBA igor.santos@citrix.com
    • Marked as answer by sqlsantos Monday, June 13, 2011 6:29 PM
    Monday, June 13, 2011 6:29 PM

All replies

  • have you tried DROP USER? i.e

    USE dbase;

    DROP USER olduser;

    GO

    Monday, June 13, 2011 4:55 PM
  • Team,
    I'm unable to drop a database user that is no longer in AD.
    This is a contractor account that left the company, and I don't have a login to associate with the database account.
    Is there a way to delete this account from my production database?
    Thanks in advance for the help.


    --- Best Regards, Igor Santos - DBA igor.santos@citrix.com

    And error message is?

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Monday, June 13, 2011 5:18 PM
  • I had to revoke VIEW DEFINITION to the database role below so I could drop the user.

    The weird thing is that I could not find this VIEW DEFINITION permission on this role, on the sys.database_permissions table, which is something I was expecting to see.

     

    select * from sys.database_permissions
    where grantor_principal_id = user_id ('domain\user');

     

    USE [BAMPrimaryImport]
    GO

    REVOKE VIEW DEFINITION ON USER::[domain\user] TO [BAM_ManagementWS] AS [domain\user]
    GO

    DROP SCHEMA [domain\user]
    GO

    DROP USER [domain\user]
    GO
    --- Best Regards, Igor Santos - DBA igor.santos@citrix.com
    • Marked as answer by sqlsantos Monday, June 13, 2011 6:29 PM
    Monday, June 13, 2011 6:29 PM