locked
Monitoring and Reporting Database Access To Tables RRS feed

  • Question

  • We have a SQL Server 2012 Server with one very sensitive database on it. We wish to retain SA access to the SQL Server but also want to report any SELECt access from the tables in the sensitive database. This will give the business users peace of mind that we will not look at the data without them knowing.

    Can we use something like Extended Events to monitor any SELECT access to tables in a database? Or maybe there is another simpler approach that can be suggested?

    Thanks in advance

    Friday, October 11, 2013 2:38 PM

Answers

  • Auditing is the right thing to use.

    Extended Events do not offer that, even though Auditing is based on the same underlying Architecture.

    To Audit all Selects on all tables, you can simply specify the Schema name within you want to audit all Selects like this:

    CREATE DATABASE AUDIT SPECIFICATION [AuditSpec_SELECT_SCH_XYZ_ROLE_public]
    FOR SERVER AUDIT [Audit_Selects]
    ADD (SELECT ON SCHEMA::[SomeSchemaName] BY [public])
    
    

    - and "public" audits access by any user...


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    • Proposed as answer by Sofiya Li Monday, October 14, 2013 8:50 AM
    • Marked as answer by Sofiya Li Monday, October 21, 2013 8:26 AM
    Friday, October 11, 2013 3:31 PM
  • Since SQL Server 2005 you can use the sys.dm_db_index_usage_stats data 
    management view, look at the column last_user_update, you can also see when 
    the table was last accessed (last_user_seek and last_user_scan - do a MAX on 
    them).

    SELECT
        last_user_seek = MAX(last_user_seek),
        last_user_scan = MAX(last_user_scan),
        last_user_lookup = MAX(last_user_lookup),
        last_user_update = MAX(last_user_update)
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        [database_id] = DB_ID()
        -- if you want to leave out system objects, uncomment the next line:
        -- AND OBJECTPROPERTY(object_id, 'IsMsShipped') = 0

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Sofiya Li Monday, October 21, 2013 8:26 AM
    Sunday, October 13, 2013 8:48 AM

All replies

  • Have a look at this thread:

    DATABASE Audit for SELECT statement [only] on a table


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    • Proposed as answer by Sofiya Li Monday, October 14, 2013 8:50 AM
    Friday, October 11, 2013 2:55 PM
  • Hi thanks,

    I am aware of this but I was under the impression you need to specify all tables individually using this method no? I was after something more dynamic where tables are being added and deleted all the time and my process will just work without any further intervention.

    Friday, October 11, 2013 2:58 PM
  • Auditing is the right thing to use.

    Extended Events do not offer that, even though Auditing is based on the same underlying Architecture.

    To Audit all Selects on all tables, you can simply specify the Schema name within you want to audit all Selects like this:

    CREATE DATABASE AUDIT SPECIFICATION [AuditSpec_SELECT_SCH_XYZ_ROLE_public]
    FOR SERVER AUDIT [Audit_Selects]
    ADD (SELECT ON SCHEMA::[SomeSchemaName] BY [public])
    
    

    - and "public" audits access by any user...


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    • Proposed as answer by Sofiya Li Monday, October 14, 2013 8:50 AM
    • Marked as answer by Sofiya Li Monday, October 21, 2013 8:26 AM
    Friday, October 11, 2013 3:31 PM
  • Since SQL Server 2005 you can use the sys.dm_db_index_usage_stats data 
    management view, look at the column last_user_update, you can also see when 
    the table was last accessed (last_user_seek and last_user_scan - do a MAX on 
    them).

    SELECT
        last_user_seek = MAX(last_user_seek),
        last_user_scan = MAX(last_user_scan),
        last_user_lookup = MAX(last_user_lookup),
        last_user_update = MAX(last_user_update)
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        [database_id] = DB_ID()
        -- if you want to leave out system objects, uncomment the next line:
        -- AND OBJECTPROPERTY(object_id, 'IsMsShipped') = 0

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Sofiya Li Monday, October 21, 2013 8:26 AM
    Sunday, October 13, 2013 8:48 AM