May 21, 2012 at 12:54 pm
On our SQL Server 2005 (Standard Edition) Server, I have the following Trigger set up to alert me (via email) if a Create Database Event happens.
Create TRIGGER [Create_Database_Trigger_]
ON ALL Server
WITH EXECUTE AS 'sa'
FOR Create_Database -- Captures a Create Database Event
AS
DECLARE
@EventType varchar(128),
@PostTime datetime,
@SPID int,
@ServerName varchar(128),
@LoginName varchar(128),
@DatabaseName varchar(128),
@TSQLCommand varchar(128),
@mySubject varchar(300),
@msg XML
SET @msg = EVENTDATA()
--load our values to variables in case we need them
SELECT
@EventType = @msg.value('(/EVENT_INSTANCE/EventType)[1]' ,'varchar(128)' ),
@PostTime = @msg.value('(/EVENT_INSTANCE/PostTime)[1]' ,'datetime' ),
@SPID = @msg.value('(/EVENT_INSTANCE/SPID)[1]' ,'int' ),
@ServerName = @msg.value('(/EVENT_INSTANCE/ServerName)[1]' ,'varchar(128)' ),
@LoginName = @msg.value('(/EVENT_INSTANCE/LoginName)[1]' ,'varchar(128)' ),
@DatabaseName = @msg.value('(/EVENT_INSTANCE/DatabaseName)[1]' ,'varchar(128)' ),
@TSQLCommand = @msg.value('(/EVENT_INSTANCE/TSQLCommand)[1]' ,'varchar(128)' )
-- Print the message to the user
SET @mySubject = 'Database ' + @DatabaseName + ' Created on ' + @@SERVERNAME + '';
PRINT @mySubject
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''
, @recipients = , @subject = @mySubject
, @body = 'Database must be Added to the NetBackup Backup Scripts on K - See Subject line of this email for Database Name'
, @importance = 'high';
can anyone tell me what I would change to Trigger on when any Database (Instance-wide) goes into Read-Only Mode?
May 21, 2012 at 1:39 pm
I'd think alter database, with a check to see what changed.
May 21, 2012 at 1:52 pm
You set a DB to read only like this:
ALTER DATABASE [My_Databse] SET READ_WRITE WITH NO_WAIT
So I would change the Trigger to:
Create TRIGGER [Alter_Database_Trigger_]
ON ALL Server
WITH EXECUTE AS 'sa'
FOR Alter_Database AS ....... ??
Sorry.. I just don't get it :unsure:
May 21, 2012 at 2:00 pm
The rest of the trigger is the same. You process the EVENT data. Not every ALTER DATABASE will include a read only option, so you need to parse the event data that comes back to determine if the database was changed.
Potentially you get the db name and then check if it's read only in your trigger code. The event data might include the change, but might not. You have to fire the event and see what is returned.
When you start, just print the data.
Create TRIGGER [ALTER_Database_Trigger_DBADistributed]
ON ALL Server
WITH EXECUTE AS 'sa'
FOR ALTER_Database -- Captures a Create Database Event
AS
DECLARE
@EventType varchar(128),
@PostTime datetime,
@SPID int,
@ServerName varchar(128),
@LoginName varchar(128),
@DatabaseName varchar(128),
@TSQLCommand varchar(128),
@mySubject varchar(300),
@msg XML
SELECT
EVENTDATA()
--load our values to variables in case we need them
, @msg.value('(/EVENT_INSTANCE/EventType)[1]' ,'varchar(128)' ),
@msg.value('(/EVENT_INSTANCE/PostTime)[1]' ,'datetime' ),
@msg.value('(/EVENT_INSTANCE/SPID)[1]' ,'int' ),
@msg.value('(/EVENT_INSTANCE/ServerName)[1]' ,'varchar(128)' ),
@msg.value('(/EVENT_INSTANCE/LoginName)[1]' ,'varchar(128)' ),
@msg.value('(/EVENT_INSTANCE/DatabaseName)[1]' ,'varchar(128)' ),
@msg.value('(/EVENT_INSTANCE/TSQLCommand)[1]' ,'varchar(128)' )
May 21, 2012 at 2:03 pm
Thank you Steve 😀
May 21, 2012 at 2:06 pm
i just tested this on my dev server;
i'm just printing some of the data available, which you would do as an email, or logging to an audit table;
you can rollback the transactiomnif you wanted to:
CREATE TRIGGER TR_ALLSERVER_FOR_ALTER_DATABASE ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR ALTER_DATABASE
AS
BEGIN
--the fields in the XML depend on the event itself
--see http://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd
DECLARE
@EventType varchar(128),
@PostTime datetime,
@SPID int,
@ServerName varchar(128),
@LoginName varchar(128),
@UserName varchar(128),
@DatabaseName varchar(128),
@SchemaName varchar(128),
@ObjectName varchar(128),
@ObjectType varchar(128),
@TSQLCommand varchar(max),
@msg XML
--load our values to variables in case we need them
SELECT @msg = EVENTDATA()
SELECT
@EventType = @msg.value('(/EVENT_INSTANCE/EventType)[1]' ,'varchar(128)' ),
@PostTime = @msg.value('(/EVENT_INSTANCE/PostTime)[1]' ,'datetime' ),
@SPID = @msg.value('(/EVENT_INSTANCE/SPID)[1]' ,'int' ),
@ServerName = @msg.value('(/EVENT_INSTANCE/ServerName)[1]' ,'varchar(128)' ),
@LoginName = @msg.value('(/EVENT_INSTANCE/LoginName)[1]' ,'varchar(128)' ),
@UserName = @msg.value('(/EVENT_INSTANCE/UserName)[1]' ,'varchar(128)' ),
@DatabaseName = @msg.value('(/EVENT_INSTANCE/DatabaseName)[1]' ,'varchar(128)' ),
@SchemaName = @msg.value('(/EVENT_INSTANCE/SchemaName)[1]' ,'varchar(128)' ),
@ObjectName = @msg.value('(/EVENT_INSTANCE/ObjectName)[1]' ,'varchar(128)' ),
@ObjectType = @msg.value('(/EVENT_INSTANCE/ObjectType)[1]' ,'varchar(128)' ),
@TSQLCommand = @msg.value('(/EVENT_INSTANCE/TSQLCommand)[1]' ,'varchar(max)' )
--now do something with this information
--the stub below just prints the event.
PRINT ' OH No! ' + @LoginName + ' = altered the database!';
PRINT ' The Command was ' + @TSQLCommand;
END --PROC
GO
ENABLE TRIGGER TR_ALLSERVER_FOR_ALTER_DATABASE ON ALL SERVER
/*
cleanup after ourselves!
DISABLE TRIGGER TR_ALLSERVER_FOR_ALTER_DATABASE ON ALL SERVER
DROP TRIGGER TR_ALLSERVER_FOR_ALTER_DATABASE ON ALL SERVER
*/
ALTER DATABASE SANDBOX SET READ_WRITE WITH NO_WAIT
/*results of p[rint!:
OH No! DISNEY\lizaguirre = altered the database!
The Command was ALTER DATABASE SANDBOX SET READ_WRITE WITH NO_WAIT
*/
Lowell
May 21, 2012 at 2:15 pm
Thank you Lowell. I will do some testing of my own on a Test server!
You guys are the best!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply