Update Your Audit Queries for SQL Server

, 2013-06-13

I was working with an auditor today who is working through a system with an external audit agency. The external agency handed us scripts to run across SQL Server, Active Directory, etc. I took on the SQL Server scripts. Then I refused to run them. The main reason I pushed back is because the scripts were valid for SQL Server 2000, but they aren't for SQL Server 2005 and above. In other words, we could like fine based on the scripts and not be fine. The scripts don't adequately check the controls on the system with regards to SQL Server. So what caught my attention?


No sys.server_permissions


CONTROL SERVER has been well documented by now. It gives the equivalent permission to being a member of the sysadmin role, with a few exceptions. If your audit script isn't checking that, your audit script needs to be updated. How do you check for it? You query sys.server_permissions.


No sys.database_permissions


The script in question used sp_helprotect. In SQL Server 2000 this was your dump permissions stored procedure. It's included in later versions of SQL Server, but it's only there for backwards compatibility. That means it returns information based on what was available in SQL Server 2000. What does it miss? Well, among other things:


  • All permissions at the schema level
  • Some permissions at the database level


Those are kind of important. If your audit script isn't checking against sys.database_permissions, your audit script needs to be updated.


Using sp_configure without turning advanced options on


There are some things that you can disable now using sp_configure. For instance:


  • xp_cmdshell
  • Use of SQL Mail


If you don't turn advanced options on, you can't check to see if these things are enabled or disabled. Turning this on and then running sp_configure is simple:


EXEC sp_configure 'show advanced options', 1;




EXEC sp_configure;


So simple that if it's not in your audit script, it should be.






Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads