Post reply

What info should a SQL Server Audit contain?

  • May 14, 2006 at 5:57 am

    #112083

    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.

  • May 15, 2006 at 10:42 am

    #637923

    Almost everything in the list could be obtained from

    On the server level:

    select name, dbid,filename form sysdatabases

    select count(name) from syslogins

    on the database level for each database:

    select

    * from sysusers where SID IN (select SID from master.dbo.syslogins)

    select * from sysfiles

    The above statements may be implemented for all databases using sp_MSForEachDB

    I would use WMI scripts for Hardware, see examples in the Microsoft Script Center

    http://www.microsoft.com/technet/scriptcenter/scripts/default.mspx

    You could use WMI scripts from the above link to query the registry for installed applications. But for the applications that are not installed, like most of web apps, you do need to talk to application support

    Regards,Yelena Varsha

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply