{"id":7098,"date":"2022-12-16T22:37:06","date_gmt":"2022-12-16T22:37:06","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/intro-to-mssql-server-audit-feature\/"},"modified":"2026-11-03T19:27:50","modified_gmt":"2026-11-03T19:27:50","slug":"intro-to-mssql-server-audit-feature","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/intro-to-mssql-server-audit-feature\/","title":{"rendered":"Intro to MSSQL Server Audit Feature"},"content":{"rendered":"

Introduction<\/strong><\/p>\n

It is always a challenge to configure audits and capture schema changes for a longer duration in the SQL Server. These details are captured by default traces; however, their retention is limited. Also, it is quite a task to set up SQL audits in every user database. It is not complicated anymore and does not require Enterprise Edition to determine who made the changes to your SQL Server.<\/p>\n

SQL Server Audits<\/strong><\/em><\/p>\n

Auditing an SQL Server or a SQL Server database involves tracking and logging events that occur. SQL Server Audit collects both server and database-level actions as well as groups of actions. Audits are performed at the instance level of the SQL Server. There can be multiple audits per SQL Server instance. Audits also include Database-Level Audit Specifications.<\/p>\n

–SQL Server Audits and its components:<\/em><\/strong><\/p>\n

SQL Server Audits<\/strong><\/p>\n

\n– It defines how event information will be kept by an audit at an instance level of the SQL server. An SQL Server audit also specifies the output locations, retentions, and other properties. At an instance level, one or more SQL Server audits are disabled by default.<\/p>\n

– Some of the options configured at SQL Server Audit Level- <\/p>\n

– Audit Destination – You can store audits to File, Event Viewer Security Logs or Event Viewer Application Logs <\/p>\n

\n    When Selecting File as your destination you can specify<\/em>
\n–    FILEPATH = Location of audit file
\n–    MAXSIZE = Max file size
\n–    MAX_FILES = numbers of file allocated for audits
\n–    RESERVE_DISK_SPACE = If you want to pre reserve file space for audits files
\n–    QUEUE_DELAY = As soon as the specified amount of time has passed, SQL Server will queue up the writes from the captured data and write them to the logs.
\n–    ON_FAILURE = It is possible to specify whether to continue to run or to stop the SQL server instance in case audits fail. An option to continue or to stop is available.
\n–    WHERE \u2013 we can use this option to filter specific events from audits.<\/p>\n

\n–    Server Audit Specification<\/strong>
\n–    One of the components of an audit is the Server Audit Specification, and it is part of the audit process. The Server Audit specifications are created at the instance level of the SQL Server. All actions from an SQL server instance and many actions from user databases can be captured by a server audit specification.<\/p>\n

\n–    In this blog, I\u2019ll be targeting the following server-level actions <\/p>\n

“`
\n–    DATABASE_ROLE_MEMBER_CHANGE_GROUP
\n–    SERVER_ROLE_MEMBER_CHANGE_GROUP        
\n–    AUDIT_CHANGE_GROUP
\n–    SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP
\n–    SERVER_OBJECT_PERMISSION_CHANGE_GROUP
\n–    SERVER_PERMISSION_CHANGE_GROUP
\n–    DATABASE_CHANGE_GROUP
\n–    DATABASE_OBJECT_CHANGE_GROUP
\n–    DATABASE_PRINCIPAL_CHANGE_GROUP
\n–    SCHEMA_OBJECT_CHANGE_GROUP
\n–    SERVER_OBJECT_CHANGE_GROUP
\n–    SERVER_PRINCIPAL_CHANGE_GROUP
\n–    LOGIN_CHANGE_PASSWORD_GROUP
\n–    SERVER_STATE_CHANGE_GROUP
\n–    DATABASE_OWNERSHIP_CHANGE_GROUP
\n–    SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP
\n–    USER_CHANGE_PASSWORD_GROUP
\n“`<\/p>\n

\n Database Audit Specifications<\/strong>
\nA database audit specification only includes actions at the database level to create or modify it in a user database. In the example shared, I\u2019ll be discussing the databases audits and try capturing schema changes in the databases using server audit specifications.<\/p>\n

The objective of these Audits :
\n1.    Capture login server-level role modification
\n2.    Capture user role assignment\/modification in all user databases
\n3.    Any changes in audits itself
\n4.    Capture Server, Database, Schema, Object level permissions changes 
\n5.    Capture Schema changes from instance to database object level
\n6.    Capture Login password changes
\n7.    Capture in server state changes <\/p>\n

Following are the steps to deploy and capture the above details using SQL Server Audits. <\/strong><\/p>\n

1.    Execute attached scripts to deploy audits and server audit specifications.<\/p>\n

\"mssql<\/p>\n

2.    This action will create SQL Server Audits with properties described in the following snip. The destination for this audit is a File with default error log location as a path.<\/p>\n

\"mssql<\/p>\n

3.    Next thing to watch is SQL Server audit specifications and you will see audit specification is created with all actions to capture as discussed.<\/p>\n

\"mssql<\/p>\n

4.    Testing audits to see if details are being captured <\/p>\n

        – a.    Create a user database and a table within the User DB<\/p>\n

\"mssql<\/p>\n

        – b.    Run one more test to change the login password-<\/p>\n

\"mssql<\/p>\n

        – c.    Let\u2019s see if records are available or not. The following snip indicates changes that are recorded in the audits.<\/p>\n

\"mssql<\/p>\n

TSQL Server Script Used For Audit: –<\/strong><\/p>\n

 <\/p>\n

USE [master] <\/em><\/p>\n

Declare @SmoRoot nvarchar(2000)
\nDeclare @String Nvarchar(max)
\nExec master.dbo.xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQLServer\\Parameters’, N’SQLArg1′, @SmoRoot OUTPUT
\nSelect @SmoRoot = SUBSTRING(@SmoRoot,3,len(@SmoRoot)-10)
\nSet @String = ‘CREATE SERVER AUDIT [MSSQL_NativeSQLAudit]
\nTO FILE 
\n(    FILEPATH = N”’+@SmoRoot+”’
\n    ,MAXSIZE = 100 MB
\n    ,MAX_FILES = 50
\n    ,RESERVE_DISK_SPACE = OFF
\n)
\nWITH
\n(    QUEUE_DELAY = 1000
\n    ,ON_FAILURE = CONTINUE
\n)
\nWHERE (NOT [statement] like ”%ALTER%INDEX%” 
\nAND NOT [statement] like ”%UPDATE%STATISTICS%” 
\nAND NOT [STATEMENT] like ”%EVENT%NOTIFICATION%” 
\nAND NOT [STATEMENT] like ”%ENABLE%TRIGGER%” 
\nAND NOT [STATEMENT] like ”%DISABLE%TRIGGER%” 
\nAND NOT [STATEMENT] like ”%OPEN%SYMMETRIC%KEY%”         
\nAND NOT [STATEMENT] like ”%SET%IDENTITY_INSERT%”         
\nAND NOT [STATEMENT] like ”%TRUNCATE%TABLE%”     
\nAND NOT [Object_name] like ”SqlQueryNotification%” 
\nAND NOT [server_principal_name] LIKE ”%\\patrol%”
\nAND NOT [STATEMENT] like ”%DBCC DBINFO WITH TABLERESULTS%” 
\nAND NOT [STATEMENT] like ”%RESTORE%VERIFYONLY%” 
\nAND NOT [STATEMENT] like ”%EXEC %%Object(%”
\nAND [database_name]<>”tempdb” 
\nAND object_id <> 1
\nAND (Object_name <> ”BlockingInfo” AND object_name <> ”MSSQL_AlwaysOn_Monitor” AND [Object_name]<>”telemetry_xevents” )
\nAND [STATEMENT] <>””)’
\n–print @String<\/em><\/p>\n

IF EXISTS (SELECT name FROM sys.server_audits WHERE NAME = ‘MSSQL_NativeSQLAudit’)
\nBEGIN
\nALTER SERVER AUDIT [MSSQL_NativeSQLAudit] WITH (STATE = OFF)
\nDROP SERVER AUDIT [MSSQL_NativeSQLAudit]
\nEND<\/em><\/p>\n

EXECUTE sp_executesql  @String<\/em><\/p>\n

IF EXISTS (SELECT name FROM sys.server_audit_specifications WHERE NAME = ‘MSSQL_ServerAuditSpecification’)
\nBEGIN
\nALTER SERVER AUDIT SPECIFICATION [MSSQL_ServerAuditSpecification] WITH (STATE = OFF)
\nDROP SERVER AUDIT SPECIFICATION [MSSQL_ServerAuditSpecification]
\nEND<\/em><\/p>\n

CREATE SERVER AUDIT SPECIFICATION [MSSQL_ServerAuditSpecification]
\nFOR SERVER AUDIT [MSSQL_NativeSQLAudit]
\nADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
\nADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
\nADD (AUDIT_CHANGE_GROUP),
\nADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
\nADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
\nADD (SERVER_PERMISSION_CHANGE_GROUP),
\nADD (DATABASE_CHANGE_GROUP),
\nADD (DATABASE_OBJECT_CHANGE_GROUP),
\nADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
\nADD (SCHEMA_OBJECT_CHANGE_GROUP),
\nADD (SERVER_OBJECT_CHANGE_GROUP),
\nADD (SERVER_PRINCIPAL_CHANGE_GROUP),
\nADD (LOGIN_CHANGE_PASSWORD_GROUP),
\nADD (SERVER_STATE_CHANGE_GROUP),
\nADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
\nADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),
\nADD (USER_CHANGE_PASSWORD_GROUP)
\nWITH (STATE = ON)<\/em><\/p>\n

ALTER SERVER AUDIT [MSSQL_NativeSQLAudit] WITH (STATE = ON)<\/em><\/p>\n

Conclusion<\/strong><\/p>\n

The SQL Server audits feature can be used in any edition to capture action not just at the instance level, but also at the database level. This is easy to configure and fulfills multiple compliance requirements. It is always important to audit a database system, and the blog will help you in doing the audit correctly.
\nThe above steps apply to the SQL 2012 Server version and above. 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"

Intro to MSSQL Server Audit Feature<\/p>\n","protected":false},"author":2,"featured_media":7100,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26255","content_type":"blog","created_by":"10130","authored_on":"2023-11-16T22:37:06+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Varun Jha, Manager Cloud Database Services Delivery, Rackspace Technology","seo_description":"Simplify SQL Server audits and schema change tracking. Overcome default trace limitations, configure event logging and enhance security.","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26255-insights_image-microsoft-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Microsoft SQL","product":"","publisheddate":"2022-12-16T22:37:06","rxt_solutions":"Data Management, Data Strategy & Governance","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"MSSQL Server Audit Compliance| Rackspace Technology","field_topics":"","field_slug":"intro-to-mssql-server-audit-feature","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7098","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7098","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=7098"}],"version-history":[{"count":2,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7098\/revisions"}],"predecessor-version":[{"id":8960,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7098\/revisions\/8960"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/7100"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=7098"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=7098"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=7098"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}