SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


is there any better way to detect sp_configure changes


is there any better way to detect sp_configure changes

Author
Message
goher2000
goher2000
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4289 Visits: 1706
Is there any better way to detect sp_configure changes

begin
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_configurations

end
-- 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
end



Sue_H
Sue_H
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75018 Visits: 15342
goher2000 - Thursday, February 8, 2018 12:31 PM
Is there any better way to detect sp_configure changes

begin
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_configurations

end
-- 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
end


That'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



Alexander Zhang
Alexander Zhang
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1139 Visits: 264
Sue_H - Thursday, February 8, 2018 2:11 PM
goher2000 - Thursday, February 8, 2018 12:31 PM
Is there any better way to detect sp_configure changes

begin
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_configurations

end
-- 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
end


That'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
goher2000
goher2000
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4289 Visits: 1706
umm... here are other two methods

-- get it from default trace
DECLARE @trc_path VARCHAR(500)
SELECT @trc_path=CONVERT(VARCHAR(500),value) FROM fn_trace_getinfo(DEFAULT)
WHERE property=2

print @trc_path
SELECT TEXTData,HostName,ApplicationName,DatabaseName,LoginName,SPID,StartTime,EventSequence
FROM fn_trace_gettable(@trc_path,1) fn
WHERE TEXTData LIKE '%Configuration option%'
-- get it from errorlog
declare @sysconfigchanges TABLE
(
RowID int IDENTITY PRIMARY KEY,
EntryTime datetime,
source varchar(50),
LogEntry varchar(4000)
)

insert into @sysconfigchanges
exec sp_readerrorlog 0,1,'Configuration option'

select * from @sysconfigchanges where rowid=(select max(rowid) from @sysconfigchanges)



Alexander Zhang
Alexander Zhang
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1139 Visits: 264
goher2000 - Friday, February 9, 2018 9:02 AM
umm... here are other two methods

-- get it from default trace
DECLARE @trc_path VARCHAR(500)
SELECT @trc_path=CONVERT(VARCHAR(500),value) FROM fn_trace_getinfo(DEFAULT)
WHERE property=2

print @trc_path
SELECT TEXTData,HostName,ApplicationName,DatabaseName,LoginName,SPID,StartTime,EventSequence
FROM fn_trace_gettable(@trc_path,1) fn
WHERE TEXTData LIKE '%Configuration option%'
-- get it from errorlog
declare @sysconfigchanges TABLE
(
RowID int IDENTITY PRIMARY KEY,
EntryTime datetime,
source varchar(50),
LogEntry varchar(4000)
)

insert into @sysconfigchanges
exec sp_readerrorlog 0,1,'Configuration option'

select * from @sysconfigchanges where rowid=(select max(rowid) from @sysconfigchanges)

Yeah, they, default trace and error log code, are both relatively good-looking:-) But, personally speaking, I prefer sys.configurations because
1. some instances probably disabled default trace
2. It might be relatively expensive to search them in error log, and may need to search multiple error logs.


GASQL.com - Focus on Database and Cloud
Sue_H
Sue_H
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75018 Visits: 15342
goher2000 - Friday, February 9, 2018 9:02 AM
umm... here are other two methods

-- get it from default trace
DECLARE @trc_path VARCHAR(500)
SELECT @trc_path=CONVERT(VARCHAR(500),value) FROM fn_trace_getinfo(DEFAULT)
WHERE property=2

print @trc_path
SELECT TEXTData,HostName,ApplicationName,DatabaseName,LoginName,SPID,StartTime,EventSequence
FROM fn_trace_gettable(@trc_path,1) fn
WHERE TEXTData LIKE '%Configuration option%'
-- get it from errorlog
declare @sysconfigchanges TABLE
(
RowID int IDENTITY PRIMARY KEY,
EntryTime datetime,
source varchar(50),
LogEntry varchar(4000)
)

insert into @sysconfigchanges
exec sp_readerrorlog 0,1,'Configuration option'

select * from @sysconfigchanges where rowid=(select max(rowid) from @sysconfigchanges)


Those are good. I don't know which one generates a bigger file or if the trace rolls over too many times to get what you need or the logs (but you can change that).
You can get more information from the trace than the log - it would have the login, not just the spid, host name, application name. You'd have a better chance of finding out who if that's important.

Sue



Sue_H
Sue_H
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75018 Visits: 15342
Alexander Zhang - Thursday, February 8, 2018 11:00 PM
Sue_H - Thursday, February 8, 2018 2:11 PM
goher2000 - Thursday, February 8, 2018 12:31 PM
Is there any better way to detect sp_configure changes

begin
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_configurations

end
-- 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
end


That'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:-)


Go ahead and try creating a table named sys.configurations in msdb. I don't doubt that those are the queries goher2000 posted.

Sue



Alexander Zhang
Alexander Zhang
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1139 Visits: 264
Sue_H - Friday, February 9, 2018 11:08 AM
Alexander Zhang - Thursday, February 8, 2018 11:00 PM
Sue_H - Thursday, February 8, 2018 2:11 PM
goher2000 - Thursday, February 8, 2018 12:31 PM
Is there any better way to detect sp_configure changes

begin
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_configurations

end
-- 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
end


That'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:-)


Go ahead and try creating a table named sys.configurations in msdb. I don't doubt that those are the queries goher2000 posted.

Sue

It shows msdb..sys_configurations to me, not msdb.sys.configurations. He corrected some typo?


GASQL.com - Focus on Database and Cloud
goher2000
goher2000
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4289 Visits: 1706
Since trace has more information as to who,where,when I have created a job called 'DBA - Notify System Changes' with the code below, Also I am using Alert to detect system changes and trigger the job.

Job Code:
-- 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

DECLARE @trc_path VARCHAR(500)
SELECT @trc_path=CONVERT(VARCHAR(500),value) FROM fn_trace_getinfo(DEFAULT)
WHERE property=2

--print @trc_path

declare @Qry varchar(8000) ='SELECT TEXTData,HostName,ApplicationName,DatabaseName,LoginName,SPID,StartTime,EventSequence FROM fn_trace_gettable('''
set @Qry=@Qry + @trc_path +''',1) fn WHERE TEXTData LIKE ''%Configuration option%'' and EventClass = 22'

--print @Qry
--exec(@Qry)

--declare @q=

EXEC msdb..sp_send_dbmail
@profile_name = @pf,
@recipients = 'sendIT2me@somewhere.com',
@subject = @sub ,
@body = 'System configuration has been changed ',
@execute_query_database = 'master',
@query = @Qry

--, @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


Alert Code :

USE [msdb]
GO

/****** Object: Alert [Alert - System Changes] Script Date: 2/9/2018 1:46:35 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert - System Changes',
@message_id=0,
@severity=10,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@event_description_keyword=N'Configuration option',
@category_name=N'[Uncategorized]',
@job_name='DBA - Notify System Changes'
GO



Sue_H
Sue_H
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75018 Visits: 15342


That's actually very nice. There have been posts up here to find the changes as well as who made those changes. I'd keep playing around with it and think about submitting that for an article. There are requests for something like this often enough that I think it would be very beneficial for the community. Generally they seem to always want to know who which is the biggest benefit to using the trace file. The disadvantage that I got burned on with it once is if the instance gets hit with a ton of things that go into the trace, it can roll over before you capture it. So make sure you run the job often enough - I do now Smile

Sue



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search