--- CREATE TRIGGER ON DATABASE CREATION
CREATE TRIGGER DDL_TRIG_CREATE_DATABASE
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @ServerName nvarchar(255)
DECLARE @Cmd nvarchar(max)
DECLARE @DBName nvarchar(255)
DECLARE @Subj nvarchar(2000)
DECLARE @XML XML
DECLARE @Recipients nvarchar(255)
DECLARE @MailProfile nvarchar(255)
SET @Recipients = 'you@yourcomapny.dk'
SET @XML = EVENTDATA()
SET @Cmd = CAST(@XML as nvarchar(MAX))
SET @ServerName = @@SERVERNAME
SET @DBName = @XML.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(200)')
SET @Subj = 'New database "' + @DBName + '" created on ' + @@SERVERNAME + ' by ' + SUSER_SNAME()
-- Find the database mail profile name
create table #dbmail_profile
(
profileid int null,
profilename varchar(125) null,
accountid int null,
accountname varchar(125) null,
sequencenumber int null
)
insert #dbmail_profile
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;
select @MailProfile = profilename
from #dbmail_profile
where sequencenumber = 1
drop table #dbmail_profile
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @MailProfile,
@recipients = @Recipients,
@subject = @Subj,
@body_format = 'TEXT',
@body = @Cmd;
Select Help,good_advice
From sys.dm_db_good_performance
These postings are provided "AS IS" with no warranties.
Wednesday, March 26, 2014
Monitoring if new databases are created
Want an email when somebody is creating a new database on your SQL Server ?
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment