Auditing sp_configure

Question

Auditing sp_configure

JWOL

Hall of Fame

Points: 3471
More actions
July 24, 2008 at 10:56 am

#123365

Hi,
Is there any way to have an e-mail alert whenever someone executes sp_configure? I know that this information is captured in the default trace but I would like to set up an alert also.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Take a look at DDL triggers in Books Online. They should be able to do what you want.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sam Greene SSCertifiable Points: 5252 More actions · Answer 2

You could deposit the trace file into a table periodically and then schedule a job to run t-sql procedure which searches for the sp_configure query. Use the sp_notify_operator(maybe not correct) to notify yourself of this activity. You have admins causing you trouble? Maybe reduce permissions to specific tasks.

JWOL Hall of Fame Points: 3471 More actions · Answer 3

I did look at http://msdn.microsoft.com/en-us/library/ms189871.aspx but sp_configure doesn't seem to be covered.

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 4

This is fired whenever anyone looks at the server properties, it does not mean that a servers properties have changed, to find that out you need to get the properties into a lookup table and periodically see if anything has changed. This script will give you the server properties:

SELECT @@servername as ServerName,

convert(nvarchar, cfg.name) AS [Name],

convert(nvarchar, cfg.minimum) AS [Minimum],

convert(nvarchar, cfg.maximum) AS [Maximum],

convert(nvarchar, cfg.value) AS [ConfigValue],

convert(nvarchar, cfg.value_in_use) AS [RunValue],

case when convert(nvarchar, cfg.is_dynamic) = '1'

and convert(nvarchar, cfg.is_advanced) = '0'

then 'Dynamic'

when convert(nvarchar, cfg.is_dynamic) = '0'

and convert(nvarchar, cfg.is_advanced) = '1'

then 'Advanced'

when convert(nvarchar, cfg.is_dynamic) = '1'

and convert(nvarchar, cfg.is_advanced) = '1'

then 'Dynamic & Advanced'

when convert(nvarchar, cfg.is_dynamic) = '0'

and convert(nvarchar, cfg.is_advanced) = '0'

then 'Static' end as Status

FROM

sys.configurations AS cfg