Around April this year, my men's accountability group was talking about a shared pain: income taxes. We were all worried that we were going to get hit with more taxes than we had planned for. Okay, that's really not a correct statement. The problem is we hadn't adequately planned at all. We all do consulting or have/had a second job and if you've ever been in either situation you know that sometimes you've not paid enough in so you avoid writing a check coming April 15th. Not only had we not adequately planned, we hadn't done a good job of keeping track of our income versus expenses. Sure, we had the receipts, but having the receipts doesn't mean you know what the final numbers are. That's the situation we found ourselves in.

We can be guilty of the same thing with respect to security and operations. We go along each day figuring we should know better than we do, but we have a bunch of excuses like no time, bigger priorities, etc. Then the audit or the incident comes and we get worried. The reason we're worried is because we likely don't have a good idea of how we'll do (in the event of an audit) or if we missed something we shouldn't have (in the event of the incident).

The key is to audit yourself regularly. Check what you know needs to be checked. Where you can, automate the checks so they run and give you the reports you need. Figure out how to summarize the data so you're not reviewing pages upon pages of raw results. For instance, if you're automating a scan of which SQL Server-based logins haven't had their passwords changed, choose a threshold that you're worried about. If the password has changed more recently than that, you don't flag it. Or, if you are interested in what changed, only flag those.

Here are some preliminary things to audit when it comes to SQL Server:

• What logins exist on the SQL Server.
• Who are the members of the key server roles (like sysadmin and securityadmin).
• What the server level permissions are.
• What databases exist on the SQL Server.
• How the logins map into the databases.
• Who are the members of the key database roles (like db_owner and db_ddladmin).

This is just a starting suggestion. What you need to audit is best determined by what your organization cares about, whether through written policy and formal audits or through informal requests you get on the job. If every once in a while your boss asks you to tell what logins were added in the last month, that would be something to build an automated report around.

Not only do the audits give you a good idea of where you stand, but they also help you flag issues or potential issues. For instance, if you see that developers are being inserted into the db_owner role for production databases and they aren't being taken out in a timely manner, that tells you there's a procedure problem somewhere. Perhaps they need those rights to troubleshoot (I'd initially argue they don't unless they could prove differently through documentation) but they don't typically need to keep those rights. Seeing developers stick around means they are getting put in but not being removed. That's not a SQL Server issue but a people issue. However, it affects the overall security of that SQL Server instance.

Audit. Audit. Audit. Don't let the results of a surprise audit be a surprise.