November 8, 2010 at 9:11 am
Hi,
I have been searching for a way to send a notification to an operator if either a CREATE DATABASE or DROP DATABASE statement was issued.
The only things I can find are DDL triggers and notifications but they are usually aimed at a specific DB. I need to know if either of those statements are issued on any database on my instance.
Is this possible, can someone post a link to some relevant documentation?
Thanks in advance,
Bodsda
November 8, 2010 at 11:06 am
DDL triggers can also fire for server-level actions, like creating/dropping databases. You just specify "on all server" instead of "on database".
- 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
November 12, 2010 at 8:07 am
Excellent, thanks for that.
I went on to write this. Let me know what you think. It's my first adventure into T-SQL
create trigger DB_CREATION
on ALL SERVER
for CREATE_DATABASE
as
declare @data xml
declare @temp1 nvarchar(max)
declare @temp2 datetime
declare @temp3 int
declare @temp4 nvarchar(max)
declare @temp5 nvarchar(max)
declare @temp6 nvarchar(max)
declare @temp7 nvarchar(max)
declare @message nvarchar(max)
declare @temp8 nvarchar(max)
set @data = eventdata()
set @temp1 = @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')
set @temp2 = @data.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
set @temp3 = @data.value('(/EVENT_INSTANCE/SPID)[1]','int')
set @temp4 = @data.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(max)')
set @temp5 = @data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)')
set @temp6 = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')
set @temp7 = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
set @temp8 = convert(nvarchar(max), @temp2, 103)
set @message = '
Event Type: ' + @temp1
+ '
Post Time: ' + @temp8
+ '
Server Name: ' + @temp4
+ '
Login Name: ' + @temp5
+ '
Database Name: ' + @temp6
+ '
Command Text: ' + @temp7
select @message
exec msdb.dbo.sp_notify_operator
@profile_name = N'Email',
@name = N'Bodsda',
@subject = N'Database created on Your Server',
@body = @message;
go
go
Thanks,
Bodsda
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply