What info should a SQL Server Audit contain? 1

[JaybeeinTekTips]

What info should a SQL Server Audit contain?

--------------------------------------------------------------------------------

That is to say, from a management perspective? I've been asked to provide one, as I'm the first DBA there ever, but I'm not sure what depth of info they want. And, does anyoe have scripts that can perform the audit, or an actual example audit?

I'd imagine an audit should encompass the following:

Database names;
Database sizes;
Location and size of data/temp/log etc. files;
Hardware specs (cpu/memory/disk sizes);
Number of logins;
User applications.

I'm sure that's only the tip of the iceberg, I'd be grateful for your input!

Thanks in advance,


Jaybee.

 

[Jamfool]

as a quick start id suggest the following:

exec [master].[dbo].xp_msver
exec [master].[dbo].sp_server_info
exec [master].[dbo].sp_configure
exec [master].[dbo].sp_helpdevice
exec [master].[dbo].sp_helpserver
exec [master].[dbo].xp_loginconfig
exec [master].[dbo].xp_logininfo
exec [master].[dbo].sp_helpdb

 

[mrdenny]

The server audit should contain what ever managment wants to see. If you simply provide them with info you have no way of knowing if that's what they want, and you get end up getting in trouble for not giving them what they want. (It does say mindreader on your resume doesn't it?)

I'd first schedule a meeting, show them what items you've come up with (not the actual audit results, but the items you are going to research). Then ask them what of those items they wish to keep, and what additional items they want to see. Also ask them what is the purpose of the Audit findings. Is it for general info, an SEC filling, a SOX audit, licensing compliance? Each of these (and there are plenty of others) will require you to give up different pieces of information.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com