Sql-server – Hypothetical: Security Intrusion

Securitysql serversql-server-2008-r2

Had a random thought I was curious about. In the case of a security breach where a user gains access to the DB server through legitimate credentials (social engineering, phishing, fired employee, etc) what are some quick and effective methods of locking down access?

Is it as simple as removing 'Connect SQL' from the securables for the login in question?

Best Answer

Simply "disable" the login: 

ALTER LOGIN [xyz] DISABLE;

Then, if the login is a SQL Server login, you can change the password with:

ALTER LOGIN [xyz] WITH PASSWORD = '$ome$ecurePa$$w0rd';

Then, re-enable the login:

ALTER LOGIN [xyz] ENABLE;

If the login is from Active Directory, you can still disable the account (and re-enable later), but you'll need to have the password changed in Active Directory.

Note that disabling the login does not disconnect anyone currently connected as that login. In order to do that, you'd need to kill the spids associated with the login.

Related Question