locked
Auditing activities performed by SQL server admin. RRS feed

  • Question

  • I am looking for some information on how to enable logging and auditing of actions performed by users on SQL server 2008 using SQL studio, such as running queries, update statements etc. This was raised as an audit finding that we need to have auditing of actions performed by users on the production database server.

     

    Thank you.

    Wednesday, December 15, 2010 9:56 PM

Answers

  • Example 1
    A relatively simple but very relevant usage of SQL Server Audit is to monitor the activity of privileged users accessing a sensitive table. This can easily be accomplished with the SQL Server Audit feature in SQL Server 2008 by setting up the proper database audit specification. For this example, assume that the sensitive table is in the hr_db database and is called hr.salary and we want to detect when the dbo (and consequently sysadmin) tries to query the table. In this example, the audit information will be written to a file on a remote share called \\AuditServer\Audit, on which the SQL Server service instance has permissions to write but not read or modify.

    First we need to create the Audit and the Database Audit Specification objects.

    Transact-SQL Copy Code
    USE master
    CREATE SERVER AUDIT audit1 TO FILE (FILEPATH='\\AuditServer\Audit')
    USE hr_db
    CREATE DATABASE AUDIT SPECIFICATION hr_dbspec FOR SERVER AUDIT audit1
    ADD(SELECT,UPDATE,INSERT,DELETE ON hr.salary by dbo)
    Because the objects are created disabled by default, they need to both be enabled.

    Transact-SQL Copy Code
    USE master
    ALTER SERVER AUDIT audit1 WITH (STATE=ON)
    USE hr_db
    ALTER DATABASE AUDIT SPECIFICATION hr_dbspec WITH (STATE=ON)
    At this point, the audit is turned on and all queries against the hr_db table by the dbo or the sysadmin are recorded, as are any actions that enable or disable the audit.

    To confirm that both the audit and the audit specification are enabled, the system views can be queried.

    Transact-SQL Copy Code
    SELECT is_state_enabled FROM sys.server_file_audits
    SELECT is_state_enabled FROM sys.database_audit_specifications


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 16, 2010 7:40 AM