The default SQL Trace<\/a> contains some useful data like the files growth events but does not have the information I needed. Plus, as mentioned in the doc, it is deprecated.<\/p>\n This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.<\/p><\/blockquote>\n Extended events<\/a> is a great performance troubleshooting tool but it does not help in this case.<\/p>\n It’s possible to use T-SQL and copy into a table the data from sys.configurations<\/a> and other DMVs related to the Agent and linked server. At a 5min interval, you can compare the current state with the previous one and send an email alert based on this delta. In most cases, this will tell you what has changed but not who did it.<\/p>\n So finally I decided to use Audits<\/a>. This feature is not widely used and sometimes underrated, but it is very powerful as we will see. So let’s start with creating a new Audit. I configure 4 rollover audit files of 50MB each located in C:\\Temp\\. Now looking at the Server Audit Specification we can see there is nothing related to Linked Server, Configuration, or Agent Jobs. So I decided to use a Database Specification on the system databases related to the audit I want to perform. Same thing for the Agent Job related stored procedure in the msdb database.<\/p>\n Now, after enabling the Audit and doing some changes, the following queries using sys.fn_get_audit_file<\/a> will show if something has been changed on my instance and who did it.<\/p>\n A similar query for linked server.<\/p>\n Having this all set up, I can run a Job that will read the audit file and send an email with everything that happened for the last 5 minutes. <\/p>\n Here is an example of the email I receive when someone changes the configuration of an instance :<\/p>\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog18_0_Audit.jpg\")
<\/a><\/p>\nAudits<\/h4>\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog18_1_newAudit.png\")
<\/p>\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog18_2_newAudit.png\")
<\/a><\/p>\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog18_3_newAudit_Server_Spec.png\")
<\/a><\/p>\n
\nFor a configuration change like “Max Server memory”, I can audit the execution of Stored Procedure “sp_configure” in the master database. For Linked Servers I will do the same with all related stored procedure.<\/p>\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog18_4_master_spec.png\")
\n<\/a>![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog18_5_master_spec.png\")
<\/a>Here is the T-SQL.<\/p>\nUSE [master]\nGO\nCREATE DATABASE AUDIT SPECIFICATION [AUDIT___DBA_SPEC_master]\nFOR SERVER AUDIT [AUDIT___DBA]\n\/* Linked Server *\/\nADD (EXECUTE ON OBJECT::[sys].[sp_addlinkedserver] BY [dbo]),\nADD (EXECUTE ON OBJECT::[sys].[sp_addlinkedsrvlogin] BY [dbo]),\nADD (EXECUTE ON OBJECT::[sys].[sp_droplinkedsrvlogin] BY [dbo]),\nADD (EXECUTE ON OBJECT::[sys].[sp_addlinkedserver] BY [dbo]),\nADD (EXECUTE ON OBJECT::[sys].[sp_addserver] BY [dbo]),\nADD (EXECUTE ON OBJECT::[sys].[sp_dropserver] BY [dbo]),\nADD (EXECUTE ON OBJECT::[sys].[sp_serveroption] BY [dbo]),\nADD (EXECUTE ON OBJECT::[sys].[sp_setnetname] BY [dbo]),\n\/* Configuration *\/\nADD (EXECUTE ON OBJECT::[sys].[sp_configure] BY [dbo])\nWITH (STATE = ON)\nGO<\/pre>\n
USE [msdb]\nGO\nCREATE DATABASE AUDIT SPECIFICATION [AUDIT___DBA_SPEC_msdb]\nFOR SERVER AUDIT [AUDIT___DBA]\n\/* Agent Jobs *\/\nADD (EXECUTE ON OBJECT::[dbo].[sp_add_schedule] BY [dbo]),\nADD (EXECUTE ON OBJECT::[dbo].[sp_add_jobstep] BY [dbo]),\nADD (EXECUTE ON OBJECT::[dbo].[sp_update_jobstep] BY [dbo]),\nADD (EXECUTE ON OBJECT::[dbo].[sp_update_jobschedule] BY [dbo]),\nADD (EXECUTE ON OBJECT::[dbo].[sp_delete_jobschedule] BY [dbo]),\nADD (EXECUTE ON OBJECT::[dbo].[sp_add_job] BY [dbo]),\nADD (EXECUTE ON OBJECT::[dbo].[sp_update_job] BY [dbo]),\nADD (EXECUTE ON OBJECT::[dbo].[sp_delete_job] BY [dbo]),\nADD (EXECUTE ON OBJECT::[dbo].[sp_stop_job] BY [dbo])\nWITH (STATE = ON)\nGO<\/pre>\n
Query Audit files<\/h4>\n
select distinct [statement]\n\t, session_server_principal_name\n\t, server_instance_name\n\t, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) AS event_time\n\t--, *\nfrom sys.fn_get_audit_file('C:\\Temp\\AUDIT___DBA*.sqlaudit', default, default)\nwhere [object_name] = 'sp_configure'\n and [statement] not like '%show advanced options%'\norder by event_time desc<\/pre>\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog18_6_sp_configure.png\")
<\/a>The Audit contains all the information I need. The login “LAB\\Administrator” changed the “Max Dregree of parallelism” setting to value 4.<\/p>\nselect DISTINCT [statement]\n\t, session_server_principal_name\n\t, server_instance_name\n\t, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) AS event_time\n\t--, *\nfrom sys.fn_get_audit_file('C:\\Temp\\AUDIT___DBA*.sqlaudit', default, default)\nwhere [object_name] IN ('sp_addlinkedserver', 'sp_addlinkedsrvlogin', 'sp_droplinkedsrvlogin'\n\t, 'sp_dropserver', 'sp_addserver')\norder by event_time desc<\/pre>\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog18_9_linked_server.png\")
<\/a>And finally for SQL Server Agent Job.<\/p>\nselect DISTINCT [statement]\n\t, session_server_principal_name\n\t, server_instance_name\n\t, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) AS event_time\n\t--, *\nfrom sys.fn_get_audit_file('C:\\Temp\\AUDIT___DBA*.sqlaudit', default, default)\nwhere [object_name] IN ('sp_add_job', 'sp_add_jobstep', 'sp_update_job', 'sp_delete_job', 'sp_stop_job'\n\t, 'sp_update_jobschedule', 'sp_delete_jobschedule', 'sp_update_jobstep', 'sp_add_schedule')\norder by event_time desc<\/pre>\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog18_7_agent_jobs.png\")
<\/a><\/p>\nEmail<\/h4>\n
\nI decided to do an email for each type of audited element (Configuration, Agent Jobs, etc..).
\nThe T-SQL script is quite simple:<\/p>\n--\tCheck interval\nDECLARE @CheckPeriodInMinute int = 5\n\nDECLARE @profile_mail varchar(128)= 'DBA_Profile'\n , @subject_mail varchar(128)= 'AUDIT - Configuration change ('+convert(varchar(256),@@servername)+')'\n\t , @recipient_mail varchar(128)=' sql@DBA.local'\n\t , @html_header varchar(max), @html varchar(max), @html_part varchar(max), @html_footer varchar(max)\n\nDECLARE @Event TABLE (\n\t [statement] varchar(503)\n\t,session_server_principal_name sysname\n\t,server_instance_name sysname\n\t,event_time datetime\n)\n\ninsert into @Event\n\tselect distinct IIF(LEN([statement])>500, LEFT([statement], 500)+'...', [statement]) AS [statement]\n\t\t, session_server_principal_name\n\t\t, server_instance_name\n\t\t, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) AS event_time\n\tfrom sys.fn_get_audit_file('C:\\Temp\\AUDIT___DBA*.sqlaudit', default, default)\n\twhere [object_name] = 'sp_configure'\n\t and [statement] not like '%show advanced options%'\n\t and DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) > DATEADD(MINUTE, -@CheckPeriodInMinute, GETDATE())\n\torder by event_time desc\n\nif @@ROWCOUNT>0\nbegin\n\tset @html_header='<head>\n\t<meta http-equiv=\"Content-Type\" content=\"text\/html; charset=utf-8\"><style type=\"text\/css\">\n\tbody {font:9pt Arial,Helvetica,sans-serif; color:black; background:White;}\n\ttable {font:9pt Arial,Helvetica,sans-serif; color:Black; background:#C0C0C0; padding:0px 0px 0px 0px; margin:1px 1px 1px 1px;width:80%}\n\ttr,td {font:9pt Arial,Helvetica,sans-serif; color:Black; background:#F5F5F5; padding:0px 0px 0px 0px; margin:1px 1px 1px 1px;}\n\tth {font:bold 9pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:2px 2px 2px 2px;}\n\tth.th2 {font:bold 9pt Arial,Helvetica,sans-serif; color:#333399; background:#ddddaa; padding:2px 2px 2px 2px;}\n\th1 {font:bold 16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:15pt; margin-bottom:0pt; padding:0px 0px 0px 0px;}\n\th2 {font:bold 14pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;}\n\tp {font:9pt Arial,Helvetica,sans-serif; color:black; background:White;}\n\t<\/style>\n\t<\/head>\n\t<body>\n\t'\n\tset @html_footer='<p><i>Executed on '+convert(varchar,getdate(),120)+' from server \"'+isnull(convert(varchar(128),@@SERVERNAME),'')+'\"<\/i><\/p>'\n\n\tSET @html=@html_header+'<h1>AUDIT - Configuration change<\/h1>\n\t<p><i>Check done on last '+convert(varchar(20),@CheckPeriodInMinute)+' minutes<\/i><\/p>\n\t<table>\n\t<tr>\n\t <th>statement<\/th>\n\t <th>session_server_principal_name<\/th>\n\t <th>server_instance_name<\/th>\n\t <th>event_time<\/th>\n\t<\/tr>\n\t'\n\n\tdeclare c1 cursor for\n\t\tselect \n\t\t'<tr>\n\t\t <td>'+[statement]+'<\/td>\n\t\t <td>'+session_server_principal_name+'<\/td>\n\t\t <td>'+server_instance_name+'<\/td>\n\t\t <td>'+CONVERT(varchar, event_time, 120)+'<\/td>\n\t\t<\/tr>\n\t\t'\n\t\tfrom @Event\n\t\torder by event_time desc\n\n\topen c1\n\tfetch c1 into @html_part\n\twhile @@FETCH_STATUS=0\n\tbegin\n\t\tset @html=@html+@html_part\n\n\t\tfetch c1 into @html_part\n\tend\n\tclose c1\n\tdeallocate c1\n\n\tset @html=@html+'<\/table>\n\t'+@html_footer\n\n\texec msdb.dbo.sp_send_dbmail \n\t\t @profile_name=\t@profile_mail\n\t\t, @recipients=\t\t@recipient_mail\n\t\t, @subject=\t\t\t@subject_mail\n\t\t, @body=\t\t\t@html\n\t\t, @body_format=\t\t'HTML'\nend<\/pre>\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog18_8_email.png\")
<\/a><\/p>\n