July 12, 2012 at 2:20 am
Hi Folks,
We recently had a problem whereby a database was dropped from an instance and noone held their hand up to it. Although with the evidence we had, we could make guesses about how it occured, guesses can't help us learn about how to prevent such problems in the future.
To that end, I have decided to write a trigger to monitor the creation and deletion of databases in the instance. The trigger prevents both events from being executed and the date and name of the person executing the event is inserted into a holding table. My question is this:
How can I insert the TSQL statement that this person has executed into the holding table? Or at least the event (DROP or CREATE) and the name of the database affected.
Many thanks in advance!
Regards,
Kev
July 12, 2012 at 2:59 am
With something like the below
--CREATE A LOGIN WHICH WILL BE USED AS AN IMPERSONATION ACCOUNT FOR PEOPLE TO SEND MAIL FOR THIS TRIGGER ONLY
USE [MASTER]
GO
CREATE LOGIN [mail] WITH PASSWORD='', DEFAULT_DATABASE=[msdb], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
--GIVE THE IMPERSONATION ACCOUNT ACCESS TO MSDB AND DATABASEMAIL
USE [msdb]
GO
CREATE USER [mail] FOR LOGIN [mail]
GO
EXEC sp_addrolemember 'DatabaseMailUserRole', 'mail'
GO
--GRANT PUBLIC IMPERSONATION RIGHTS TO THE IMPERSONATION ACCOUNT SO THAT THE TRIGGER CAN SEND THE MAIL
GRANT IMPERSONATE ON LOGON::mail to PUBLIC
GO
--CREATE A LOGGING DATABASE
/*
CREATE DATABASE DBLogging .................................................
*/
GO
--CREATE THE LOGGING TABLE
CREATE TABLE [dbo].[DatabaseLog](
[DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
[PostTime] [datetime] NOT NULL,
[DatabaseUser] [sysname] NOT NULL,
[Event] [sysname] NOT NULL,
[DatabaseName] [sysname] NOT NULL,
[Schema] [sysname] NULL,
[Object] [sysname] NULL,
[TSQL] [nvarchar](max) NOT NULL,
[XmlEvent] [xml] NOT NULL,
CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED
(
[DatabaseLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--CREATE THE IMPERSONATION ACCOUNT IN THE LOGGING DATABASE AND ASSIGN WRITE ACCESS TO THE LOGGING TABLE
USE [LOGGINGDATABASE]
GO
CREATE USER [mail] FOR LOGIN [mail]
GO
GRANT INSERT ON DBO.DatabaseLog to mail
GO
--CREATE THE SERVER SIDE DDL TRIGGER TO PREVENT ANY DDL CHANGES
--REMEMBER TO CHANGE THE MSDB.DBO.SP_SEND_DBMAIL LINE TO FIT IN WITH THE SYSTEM SETUP
CREATE TRIGGER [antg] ON ALL SERVER
FOR DDL_EVENTS
AS
IF IS_SRVROLEMEMBER('sysadmin') = 0
BEGIN
DECLARE @data XML;
DECLARE @schema SYSNAME;
DECLARE @object SYSNAME;
DECLARE @eventType SYSNAME;
DECLARE @user SYSNAME;
DECLARE @Mess NVARCHAR(MAX);
DECLARE @dbname SYSNAME;
SET @user = SUSER_NAME()
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'SYSNAME');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME');
SET @dbname = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','SYSNAME');
PRINT 'Only SysAdmins can modify this server and it'+char(39)+'s databases'
PRINT 'You'+char(39)+'r statement has been logged and emailed to the DBA team'
ROLLBACK TRANSACTION
EXECUTE AS LOGIN = 'mail'
INSERT ###################.dbo.[DatabaseLog]
(
[PostTime],
[DatabaseUser],
[Event],
[DatabaseName],
[Schema],
[Object],
[TSQL],
[XmlEvent]
)
VALUES
(
GETDATE(),
CONVERT(SYSNAME, @user),
@eventType,
@dbname,
CONVERT(SYSNAME, @schema),
CONVERT(SYSNAME, @object),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
);
SET @Mess =
'<FONT FACE="Arial"> User = ' + '<B>' + UPPER(CONVERT(SYSNAME, @user)) + '</B>' + ' has tried to do the following DDL operation'
+ '
'
+ 'DDL Operation = ' + '<B>' + @eventType + '</B>'
+ '
'
+ 'Object = ' + '<B>' + @dbname + '.' + CONVERT(SYSNAME, @schema) + '.' + CONVERT(SYSNAME, @object) + '</B></FONT>';
SELECT @Mess
EXECUTE MSDB.DBO.sp_send_dbmail @profile_name = , @recipients = '', @subject = 'Server level change detected and rolled back', @body = @Mess, @body_format = 'HTML'
REVERT
END
Just need to change it to do what you want, either leave emailing in or take it out etc etc.
July 12, 2012 at 9:31 am
If you found it soon enough the Default Trace will have logged the DROP DATABASE and it includes the user who did the drop. The issue is that after a couple of days the Default Trace files will likely have wrapped so it won't have that data any longer. This query will get you the information:
With cteObjectTypes AS
(
SELECT
TSV.trace_event_id,
TSV.subclass_name,
TSV.subclass_value
FROM
sys.trace_subclass_values AS TSV JOIN
sys.trace_columns AS TC ON
TSV.trace_column_id = TC.trace_column_id
WHERE
TC.[name] = 'ObjectType'
),
cteEventSubClasses AS
(
SELECT
TSV.trace_event_id,
TSV.subclass_name,
TSV.subclass_value
FROM
sys.trace_subclass_values AS TSV JOIN
sys.trace_columns AS TC ON
TSV.trace_column_id = TC.trace_column_id
WHERE
TC.[name] = 'EventSubClass'
)
SELECT
TE.[name],
I.ApplicationName,
I.BigintData1,
I.ClientProcessID,
I.ColumnPermissions,
I.DatabaseID,
I.DatabaseName,
I.DBUserName,
I.Duration,
I.EndTime,
I.Error,
I.EventSequence,
Convert(nvarchar(10), I.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,
I.FileName,
I.HostName,
I.IndexID,
I.IntegerData,
I.IsSystem,
I.LineNumber,
I.LoginName,
I.LoginSid,
I.NestLevel,
I.NTDomainName,
I.NTUserName,
I.ObjectID,
I.ObjectID2,
I.ObjectName,
Convert(nvarchar(10), I.ObjectType) + N'-' + OT.subclass_name as ObjectType,
I.OwnerName,
I.ParentName,
I.Permissions,
I.RequestID,
I.RoleName,
I.ServerName,
I.SessionLoginName,
I.Severity,
I.SPID,
I.StartTime,
I.State,
I.Success,
I.TargetLoginName,
I.TargetLoginSid,
I.TargetUserName,
I.TextData,
I.TransactionID,
I.Type,
I.XactSequence
FROM
sys.traces T CROSS Apply
sys.fn_trace_gettable(CASE WHEN PATINDEX('%Log[_]%', path)<> 0
THEN LEFT(path, PATINDEX('%Log[_]%', path) + 2) +
'.trc'
ELSE T.[path]
END, T.max_files) I JOIN
sys.trace_events AS TE ON
I.EventClass = TE.trace_event_id LEFT JOIN
cteEventSubClasses AS ESC ON
TE.trace_event_id = ESC.trace_event_id And
I.EventSubClass = ESC.subclass_value LEFT JOIN
cteObjectTypes AS OT ON
TE.trace_event_id = OT.trace_event_id AND
I.ObjectType = OT.subclass_value
WHERE
T.is_default = 1 AND
TE.NAME = 'Object:Deleted' AND
I.ObjectType = '16964' /* This is DB */
this won't keep it from happening but it does give you the chance to find out who. THen they need to learn to fess up because it is always better to do that than be found out later.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 14, 2012 at 3:35 am
Create this Trigger on master db
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Security]
ON ALL SERVER
FOR DROP_database
AS PRINT 'Database Dropped'
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
ROLLBACK --this will rollback the drop transaction
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [Security] ON ALL SERVER
GO
& if anyone tried to drop your database he will got the following result like this:
DROP DATABASE MyDatabase
(1 row(s) affected)
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
July 16, 2012 at 1:55 am
Hi Folks!
Firstly, thanks for the posts so far! It's always interesting to see how different people approach the same problem from different angles!
I have created the trigger now (some of you might recognise some of the code!) and this is the result:
---------------------------------------------------------------
--CREATE THE HOLDING TABLE FOR THE TRIGGER
---------------------------------------------------------------
USE Monitor;
GO
CREATE TABLE dbo.ServerDBsMonitor_Log (
NTUserNamevarchar(50)
,SQLServerUservarchar(50)
,ServerNamevarchar(50)
,EventTimeDateTime
,TSQLTextXML
);
insert into dbo.ServerDBsMonitor_Log (NTUserName, EventTime) values ('Log Start', CURRENT_TIMESTAMP);
SELECT * FROM dbo.ServerDBsMonitor_Log;
go
---------------------------------------------------------------
--PREVENT DB DROP FOR ALL USERS
---------------------------------------------------------------
--CREATE THE TRIGGER TO PREVENT THE DROPPING OF SQL SERVER DATABASES
USE Monitor;
go
CREATE TRIGGER ServerDBsMonitor_trigger ON ALL SERVER
FOR DROP_DATABASE
,CREATE_DATABASE
AS
--THE FOLLOWING PREVENTS ALL USERS DROPPING OR CREATING DATABASES
IF
IS_MEMBER ('db_owner') = 0 or IS_MEMBER ('db_owner') = 1
BEGIN
PRINT 'Sie haben nicht die Berechtigung Datenbanken zu Erstellen oder Löschen! Wenden Sie an Ihre Datenbankadministrator!'
ROLLBACK TRANSACTION;
END
--POPULATE THE HOLDING TABLE WITH INFORMATION RELATING TO THE PERSON ATTEMPTING
--TO DROP OR CREATE A DATABASE ON THIS SERVER
BEGIN
INSERT INTO Monitor.dbo.ServerDBsMonitor_Log (
NTUserName--Windows LogIn Name
,SQLServerUser--SQL Server LogIn Name
,ServerName--The Workstation from which the command was executed
,EventTime--The time of the event
,TSQLText)--The command that was executed
VALUES (
SUSER_NAME()
,CURRENT_USER
,@@SERVERNAME
,CURRENT_TIMESTAMP
,(SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
))
END;
GO
Although the trigger does essentially what it is supposed to (prevents CREATE_DATABASE and DROP_DATABASE along with logging the username and event time into a separate table), it doesn't log the code that was attempted.
When I attempt a DROP DATABASE for example, the DROP fails (as it should) and information is logged as shown below:
NTUserNameSQLServerUserServerNameEventTimeTSQLText
Log StartNULLNULL2012-07-16 09:40:48.633NULL
CBR\adminkbdboCBR-SQLNODE1\MGMT_SQL12012-07-16 09:40:59.630NULL
CBR\adminkbdboCBR-SQLNODE1\MGMT_SQL12012-07-16 09:54:01.257NULL
Can anyone explain please why the TSQLText column return NULL?
Regards to all,
Kev
July 16, 2012 at 1:59 am
Remove the /CommandText from the TSQL insert line.
should look like this
value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
July 16, 2012 at 2:43 am
Hi Anthony,
Thanks for the tip but it still returns NULL.....somewhat confusing...!
Regards,
kev
July 16, 2012 at 2:50 am
Your wrapping it in a select
Try this, if not set eventdata() into a variable then use the variable in the insert instead of the eventdata() function.
--CREATE THE TRIGGER TO PREVENT THE DROPPING OF SQL SERVER DATABASES
USE Monitor;
go
CREATE TRIGGER ServerDBsMonitor_trigger ON ALL SERVER
FOR DROP_DATABASE
,CREATE_DATABASE
AS
--THE FOLLOWING PREVENTS ALL USERS DROPPING OR CREATING DATABASES
IF
IS_MEMBER ('db_owner') = 0 or IS_MEMBER ('db_owner') = 1
BEGIN
PRINT 'Sie haben nicht die Berechtigung Datenbanken zu Erstellen oder Löschen! Wenden Sie an Ihre Datenbankadministrator!'
ROLLBACK TRANSACTION;
END
--POPULATE THE HOLDING TABLE WITH INFORMATION RELATING TO THE PERSON ATTEMPTING
--TO DROP OR CREATE A DATABASE ON THIS SERVER
BEGIN
INSERT INTO Monitor.dbo.ServerDBsMonitor_Log (
NTUserName --Windows LogIn Name
,SQLServerUser --SQL Server LogIn Name
,ServerName --The Workstation from which the command was executed
,EventTime --The time of the event
,TSQLText) --The command that was executed
VALUES (
SUSER_NAME()
,CURRENT_USER
,@@SERVERNAME
,CURRENT_TIMESTAMP
,EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)')
)
END;
GO
July 16, 2012 at 6:02 am
Hello again Anthony!
It is still returning NULL! My understanding is that the EVENTDATA() function is populated with the command that activated the trigger. In this case, that would be a DROP DATABASE or CREATE DATABASE.
Is that right? This is doing my head in!!!! :unsure:
Regards
Kev
July 16, 2012 at 6:14 am
all my DDL triggers are designed as putting the EVENTDATA() into an xml variable, and then querying the xml variable; not a single one of my sample triggers seem to be querying the EVENTDATA() directly.
when i redesigned your query to match my "syle", it captures the commands correctly.
try this instead.
also, there was no need to test if is_dbowner = 0 and also =1; since they are mutually exclusive, and cover all possibilities, there's no need to test that, right?
ALTER TRIGGER ServerDBsMonitor_trigger ON ALL SERVER
FOR DROP_DATABASE
,CREATE_DATABASE
AS
--Load Variables from the xml
declare
@eventData XML,
@DATABASENAME SYSNAME,
@EVENTDATE DATETIME,
@USERNAME SYSNAME,
@SYSTEMUSER VARCHAR(128),
@CURRENTUSER VARCHAR(128),
@ORIGINALUSER VARCHAR(128),
@HOSTNAME VARCHAR(128),
@APPLICATIONNAME VARCHAR(128),
@SCHEMANAME SYSNAME,
@OBJECTNAME SYSNAME,
@OBJECTTYPE SYSNAME,
@EVENTTYPE VARCHAR(128),
@COMMANDTEXT VARCHAR(max),
@NAMEFORDEFINITION VARCHAR(261)
SET @eventData = eventdata()
SELECT
@DATABASENAME = db_name(),
@EVENTDATE = GETDATE(),
@USERNAME = @eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@SYSTEMUSER = SUSER_SNAME(),
@CURRENTUSER = CURRENT_USER,
@ORIGINALUSER = ORIGINAL_LOGIN(),
@HOSTNAME = HOST_NAME(),
@APPLICATIONNAME = APP_NAME(),
@SCHEMANAME = @eventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
@OBJECTNAME = @eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@OBJECTTYPE = @eventData.value('data(/EVENT_INSTANCE/ObjectType)[1]', 'SYSNAME'),
@COMMANDTEXT = @eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(MAX)'),
@EVENTTYPE = @eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)')
--THE FOLLOWING PREVENTS ALL USERS DROPPING OR CREATING DATABASES
IF
IS_MEMBER ('db_owner') = 0 or IS_MEMBER ('db_owner') = 1
BEGIN
PRINT 'Sie haben nicht die Berechtigung Datenbanken zu Erstellen oder Löschen! Wenden Sie an Ihre Datenbankadministrator!'
ROLLBACK TRANSACTION;
END
--POPULATE THE HOLDING TABLE WITH INFORMATION RELATING TO THE PERSON ATTEMPTING
--TO DROP OR CREATE A DATABASE ON THIS SERVER
BEGIN
INSERT INTO Monitor.dbo.ServerDBsMonitor_Log (
NTUserName --Windows LogIn Name
,SQLServerUser --SQL Server LogIn Name
,ServerName --The Workstation from which the command was executed
,EventTime --The time of the event
,TSQLText) --The command that was executed
VALUES (
SUSER_NAME()
,CURRENT_USER
,@@SERVERNAME
,CURRENT_TIMESTAMP
,@COMMANDTEXT
)
END;
GO
Lowell
July 18, 2012 at 2:45 am
Hi Lowell,
Thank you very much! Now everything works exactly as it should!
Many thanks to all for your input!
Regards,
Kev
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply