none
delete login of non existent AD user

    Question

  • I am pretty sure that this is safe... but just in case want to ask.

    Can I safely delete Login for non existent anymore AD user?

    Thanks.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Monday, March 6, 2017 2:01 PM

Answers

  • If the user has already been deleted from the AD, you will find that any jobs he/she has in SQL Server agent will start or be failing. You will need to address this.

    If you delete the login from SQL Server any objects this user owns within SQL Server will be orphaned. This normally will not cause problems, unless there are triggers, stored procedures or CLR objects which are executed under this login context. Then these will start failing.

    • Proposed as answer by Uri DimantMVP Monday, March 6, 2017 2:19 PM
    • Marked as answer by pob579 Tuesday, March 7, 2017 4:21 PM
    Monday, March 6, 2017 2:17 PM
  • Yes, here is tsql which will drop windows login:

    sp_revokelogin [WINDOWSDOMAIN\user_acccount]; -- windows account

    You will want to drop the corresponding database user from any user databases before dropping the login, just to cleanup db orphans.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Marked as answer by pob579 Tuesday, March 7, 2017 4:21 PM
    Monday, March 6, 2017 2:17 PM

All replies

  • If the user has already been deleted from the AD, you will find that any jobs he/she has in SQL Server agent will start or be failing. You will need to address this.

    If you delete the login from SQL Server any objects this user owns within SQL Server will be orphaned. This normally will not cause problems, unless there are triggers, stored procedures or CLR objects which are executed under this login context. Then these will start failing.

    • Proposed as answer by Uri DimantMVP Monday, March 6, 2017 2:19 PM
    • Marked as answer by pob579 Tuesday, March 7, 2017 4:21 PM
    Monday, March 6, 2017 2:17 PM
  • Yes, here is tsql which will drop windows login:

    sp_revokelogin [WINDOWSDOMAIN\user_acccount]; -- windows account

    You will want to drop the corresponding database user from any user databases before dropping the login, just to cleanup db orphans.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Marked as answer by pob579 Tuesday, March 7, 2017 4:21 PM
    Monday, March 6, 2017 2:17 PM