Sql-server – Should I delete disabled windows Active Directory accounts from SQL 2008

active-directoryloginssql server

Is there a risk of not deleting unused Windows logins that have been disabled in AD or is it simply a housekeeping issue?

e.g. Windows authentication is used to provide database access. A user leaves the organisation. Their Active Directory account is disabled, but their SQL login remains.

Best Answer

The simplest scenario is to avoid managing individual AD accounts in SQL Server whenever possible. The most common solution is to use AD groups and provide the groups access to the correct SQL server database. Then when people join or leave the AD administrators control what their rights are within AD. This does assume that the AD team has a well defined process for managing accounts. This was the solution suggested in the comment by @swasheck. I just thought it deserved to be an answer.

Related Solutions

Sql-server – Using multiple Active Directory accounts to connect to SQL Server

Yes, the most secure means to for authentication is Windows Authentication. Read more about this topic and the differences here.

As for different applications having different logins, that is going to be the most secure method. The reason behind this is because each application will most likely require a different set of permissions (I'm assuming a bit here), and instead of having one server principal with a handful of permissions to suit numerous applications, it will be more secure to have each application have a login with permissions tailored to its own needs. It's all about the Principle of Least Privilege.

SQL Server Script – Delete Accounts Not in Active Directory

What I ended doing is listing the accounts with:

    exec sp_validatelogins

And running

    exec sp_dropuser loginname
    exec sp_droplogin loginname

on the results.

Best Answer

Related Solutions

Sql-server – Using multiple Active Directory accounts to connect to SQL Server

SQL Server Script – Delete Accounts Not in Active Directory

Related Question