Home Forums SQL Server 2014 Administration - SQL Server 2014 is there any better way to detect sp_configure changes RE: is there any better way to detect sp_configure changes
February 8, 2018 at 11:00 pm
Sue_H - Thursday, February 8, 2018 2:11 PMgoher2000 - Thursday, February 8, 2018 12:31 PMIs there any better way to detect sp_configure changesbegin
IF object_id('msdb..sys_configurations') is null
begin
select * into msdb..sys_configurations from master.sys.configurations
end-- set servername in subject line
declare @sub nvarchar(255) = 'SQL Server Instance configuration has been changed ' + cast(@@servername as nvarchar)-- pick one mail profile if there are multipal mail profiles
declare @pf nvarchar(255)
select top 1 @pf= sp.name from msdb..sysmail_profile sp
join msdb..sysmail_profileaccount spa on sp.profile_id=spa.profile_id
join msdb..sysmail_account sa on spa.account_id=sa.account_id-- send email for notification ONLY if there is difference between master.sys.configurations and msdb..sys_configurations
if exists (select * from master.sys.configurations except select * from msdb..sys_configurations)
begin
EXEC msdb..sp_send_dbmail
@profile_name = @pf,
@recipients = 'sendit2me@somewhere.com',
@subject = @sub ,
@body = 'System configuration has been changed ',
@execute_query_database = 'msdb',
@query =
'with cte_config as
(select * from master.sys.configurations except select * from msdb..sys_configurations )
select ''CHANGED FROM <<--'' as [<<<<<<<<<<<<<<] ,* from msdb..sys_configurations where configuration_id in (select configuration_id from cte_config);
with cte_config as
(select * from master.sys.configurations except select * from msdb..sys_configurations )
select ''CHANGED TO ---->>'' as [>>>>>>>>>>>>>>] ,* from cte_config'
--, @attach_query_result_as_file = 1
--, @query_attachment_filename = 'configuration.csv'
, @query_result_header = 0
, @query_result_width = 32767
, @query_result_separator = ','
, @query_result_no_padding =1-- since notification has been sent out, get rid of data as it is no longer valid
drop table msdb..sys_configurationsend
-- must create table to hold data for comparision for next run
IF object_id('msdb..sys_configurations') is null
begin
select * into msdb..sys_configurations from master.sys.configurations
end
endThat's a catalog view in all databases - it's querying the same thing (sys.configurations$) no matter where you run it so they would be the same.
Configuration changes are logged to the error log and are in the default trace as well.Sue
I think goher2000 keep prev copy in msdb, compare it to the current sys table to get the difference. I would say the script is not so good-looking, but the current main stream solution:-)
GASQL.com - Focus on Database and Cloud