• 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