Tracking SQL Server object usage

There is a number of object types in SQL Server database system. Each has its own purpose and role in proper data management. However, sometimes it is required to check how some of them are used.

There are several common scenarios when tracking SQL Server object usage can be required:

• Schema optimization – In this scenario, it’s required to verify whether an object is used at all, and if so, how it’s used and how often. This helps in overall SQL Server schema optimization as the object can be altered, moved, or even deleted from the production server. Identifying objects that are rarely or not used leads to easier SQL Server schema documenting and maintenance
• Performance optimization – It’s not only hardware to be blamed for poor SQL Server performance. Very often, rapidly used multiple objects (e.g. tables) can be the cause of performance degradation and bottlenecks. If such objects can be identified by tracking their usage, some of them can be moved to other production servers or additional optimizations can be applied to the client software
• Security monitoring – This scenario is critical when it comes to SQL Server security and compliance (e.g. Basel II, HIPAA, PCI, FERPA, GLBA, and SOX) requirements that needs to be meet. It’s required to track and verify proper security settings by identifying object usage by unauthorized users. If such entries are identified within auditing reports, additional investigation is required, both for SQL Server security and to prevent loss of the SQL Server database management system compliance status

You can utilize either native SQL Server features or 3rd party tools to track object usage in SQL server

Tracking SQL Server object usage with the Audit feature

The SQL Server Audit feature was introduced in SQL Server 2008 on top of Extended Events and it audits singe or groups of events both on server and database level. However, the database level auditing is available in SQL Server Enterprise and Developer editions only.

To track object use with the SQL Server Audit feature, it’s necessary to set up the auditing. In order to do so, an audit object must be created first. This can be done using SQL Server Management Studio or T-SQL.

The following T-SQL creates and enables the AuditObjectUsage audit object:

USE [master];
GO
CREATE SERVER AUDIT [AuditObjectUsage] TO FILE (
              FILEPATH = N'C:\AUDITs\'
            , MAXSIZE = 15 MB
            , MAX_FILES = 10
            , RESERVE_DISK_SPACE = OFF
            )
      WITH (
            QUEUE_DELAY = 1000
            , ON_FAILURE = CONTINUE
            );
      ALTER SERVER AUDIT [AuditObjectUsage]
      WITH (STATE = ON);
GO

Within the T-SQL we specified that audited information will be stored in maximum 10 files (each 15 MB in size), located in the AUDITs sub-folder on the C drive. This can be changed according to your needs and resource capabilities.

The next step is to set up the auditing in the particular database on specific objects. This is where availability of the SQL Server Audit feature is required on the database level.

To continue setting up the auditing, it’s required to create a database level audit specification. Such database level audit specification will belong to the audit object (AuditObjectUsage) we previously created. The following T-SQL creates and enables the database level audit specification:

USE [ACMEDBNEW];
GO
CREATE DATABASE AUDIT SPECIFICATION [ObjectUseSpecification]
      FOR SERVER AUDIT [AuditObjectUsage]
      ADD (DELETE ON OBJECT::dbo.Customers BY [public]),
      ADD (INSERT ON OBJECT::dbo.Customers BY [public]),
      ADD (SELECT ON OBJECT::dbo.Customers BY [public]),
      ADD (UPDATE ON OBJECT::dbo.Customers BY [public]),
      ADD (EXECUTE ON OBJECT::dbo.Customers BY [db_owner]),
      ADD (EXECUTE ON OBJECT::dbo.Invoices BY [db_securityadmin])
      WITH (STATE = ON);
GO

Within the T-SQL we specified that the Customers table will be audited for particular actions (SELECT/INSERT/UPDATE/DELETE/EXECUTE), while the Invoices table will be audited for EXECUTE operations only. Note that it’s possible to specify only one object and one principal per event.

Although SQL Server provides a built-in feature (the View Audit Logs context menu option of an audit object) to view captured information, this is not a convenient way for creating comprehensive reports, and it provides basic filtering only.

So, in order to provide tracked information for any deeper analysis or documenting, use the fn_get_file_audit SQL Server function to read repository .sqlaudit files used by the audit object. The following T-SQL script queries the information tracked by the AuditObjectUsage server level audit object:

SELECT
       event_time AS [Event time],
       session_server_principal_name AS [User name] ,
       server_instance_name AS [Server name],
       database_name AS [Database name],
       object_name AS [Audited object],
       statement AS [T-SQL statement]
  FROM sys.fn_get_audit_file('C:\AUDITs\AuditObjectUsage*.sqlaudit', DEFAULT, 
DEFAULT);

The SQL Server Audit feature has its advantages and disadvantages. It is native, but
when it comes to tracking database level objects, supported by SQL Server Enterprise and Developer editions only You can use either SQL Server Management Studio GUI or T-SQL to set up auditing objects and specifications, but none of these means provide centralized auditing set up process. It’s easy to omit objects and events required to be tracked since each event-object-principal combination must be explicitly specified.

Regarding the compliance regulations and their requirements, the SQL Server Audit feature doesn’t provide tamper-evident repository as required. And again, T-SQL knowledge is required for captured information analysis, creating, and customizing reports per compliance revision or auditor requests.

The auditing solution for tracking SQL Server object usage

ApexSQL Audit is a compliance tool for SQL Server that features a range of auditing and documenting captured information options via a user-friendly GUI. It helps ensuring SQL Server security and requirements for compliance regulations by tracking changes and access to objects on one or more SQL Server instances.

To track SQL Server object usage on a specific database:

1. Start ApexSQL Audit
2. Select the SQL Server instance and its database to audit
3. To track object usage, check the DML, Query, and Execute options in the Operations pane

4. Select/check the particular objects or object types required to be tracked from the Objects dialog

   

5. If tracking of multiple SQL Server instances or databases is required, repeat previously described steps 2, 3, and 4 accordingly on appropriate instances, databases, and objects

6. Finally, to confirm newly set auditing settings click the Apply option in the pop-up at the top of the ApexSQL Audit GUI

   

There is a built-in report template that shows information about objects usage – the DML history report, found in the Changes and activity auditing group.

To access the audited information, use the Reports option in the ApexSQL Audit main toolbar and select the described report from the reports list . Also, if additional auditing documentation is required, which is common during compliance and auditing revisions, ApexSQL Audit features a custom report designer

The process of monitoring SQL Server objects usage with ApexSQL Audit includes:

• User friendly configuration set up via a point-and-click GUI
• Tracking and documenting without T-SQL knowledge
• Automatic monitoring of any object usage by any user, regardless of the SQL Server database management system role the user belongs to
• Identification of compliance problems and potential security risks
• A centralized captured information storage with the archiving feature
• Comprehensive, tamper evident, accurate, and customizable reports for reviews and analysis
• Support for all SQL Server editions

SQL Server objects usage and compliance requirements can be achieved via the SQL Server Audit feature or via 3rd party tools. However, the native method doesn’t provide a tamper evident storage, requires advanced T-SQL knowledge for configuring auditing and documenting, and is supported by few SQL Server editions. On the other hand, ApexSQL Audit provides an efficient tracking and documenting process, along with centralized support for multiple SQL Server instances, databases, and their objects

Resources:
SQL Server Audit Action Groups and Actions
Microsoft SQL Server 2008 Bible, Wiley Publishing, Inc.
A White Paper Proposing Practical, Cost Effective Compliance Strategies

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.