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;

GO
RECONFIGURE;
GO
EXEC sp_configure;

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