CODEWITHSUNDEEP
sql-server
Frode Lillerud
Frode Lillerud

Reputation: 7394

Find who/when a SQL view is dropped in SQL Server

In one of our SQL Server databases we have many SQL views. One particular view keeps disappearing every few weeks, and I want to find out what is happening.

Is there a way to query SQL Server to find out when and who dropped the view?

Alternatively, is it possible to add a SQL Server trigger on the DROP view command to capture and fail the DROP?

Upvotes: 0

Views: 2631

Answers (4)

jthalliens
jthalliens

Reputation: 544

You can also create a trigger at the server level in order to capture and log DDL changes on the database :

CREATE TRIGGER [Trg_AuditStoredProcedures_Data]
ON ALL SERVER
FOR     CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE,CREATE_TABLE,ALTER_TABLE,
DROP_TABLE,CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION,CREATE_VIEW,ALTER_VI    EW,
DROP_VIEW,CREATE_DATABASE,DROP_DATABASE,ALTER_DATABASE,
CREATE_TRIGGER,DROP_TRIGGER,ALTER_TRIGGER

AS


SET ANSI_PADDING ON

DECLARE @eventdata XML;
SET @eventdata = EVENTDATA(); 

SET NOCOUNT ON


/*Create table AuditDatabaseObject in order to have a history tracking for every DDL change on the database*/
INSERT INTO AuditDatabaseObject 
(DatabaseName,ObjectName,LoginName,ChangeDate,EventType,EventDataXml,HostName)
VALUES (
  @eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname')
, @eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
, @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')
, GETDATE()
, @eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname')
, @eventdata
, HOST_NAME()
);

DECLARE @Valor VARCHAR(30),@EvenType VARCHAR(30)
SET @Valor = @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')
SET @EvenType = @eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname')

IF (IS_SRVROLEMEMBER('sysadmin',@Valor) != 1 AND @EvenType = 'DROP_DATABASE') 
BEGIN
    ROLLBACK 
END

you can find more information here EVENTDATA()

if an object is dropped from the database you will see a record created on the table AuditDatabaseObject

enter image description here

also keep in mind security as @ Chris Pickford mentioned.

Upvotes: 0

Dave Mason
Dave Mason

Reputation: 4956

To expand on another answer, here is some code to get started with a DDL trigger for DROP_VIEW. As an example, let's suppose someone dropped the view [HumanResources].[vEmployee] from the AdventureWorks database. The EVENTDATA() will look something like this:

<EVENT_INSTANCE>
  <EventType>DROP_VIEW</EventType>
  <PostTime>2016-02-26T09:02:58.190</PostTime>
  <SPID>60</SPID>
  <ServerName>YourSqlHost\SQLEXPRESS</ServerName>
  <LoginName>YourDomain\SomeLogin</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>AdventureWorks2012</DatabaseName>
  <SchemaName>HumanResources</SchemaName>
  <ObjectName>vEmployee</ObjectName>
  <ObjectType>VIEW</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>DROP VIEW [HumanResources].[vEmployee]
</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

And here is a possible DDL trigger statement:

CREATE TRIGGER trgDropView
ON DATABASE
FOR DROP_VIEW
AS
BEGIN

--Grab some pertinent items from EVENTDATA()
DECLARE @LoginName NVARCHAR(MAX) = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(MAX)')
DECLARE @TsqlCmd NVARCHAR(MAX) = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(MAX)')

--Now do something.  Lots of possibilities.  Here are two:

--1) Send Email
DECLARE @Subj NVARCHAR(255) = @@SERVERNAME + ' - VIEW DROPPED'
DECLARE @MsgBody NVARCHAR(255) = 'Login Name: ' + @LoginName + CHAR(13) + CHAR(10) +
    'Command: ' + @TsqlCmd
EXEC msdb..sp_send_dbmail
    @recipients = '[email protected]', 
    @subject = @Subj,
    @body = @MsgBody

--2) Log an error
DECLARE @ErrMsg NVARCHAR(MAX) = @@SERVERNAME + ' - VIEW DROPPED' + CHAR(13) + CHAR(10) +
    'Login Name: ' + @LoginName + CHAR(13) + CHAR(10) +
    'Command: ' + @TsqlCmd
RAISERROR(@ErrMsg, 16, 1) WITH LOG;

END

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46425

This information is written to the default trace. Below is an example query to glean the information.

SELECT 
     te.name
    ,tt.DatabaseName
    ,tt.StartTime
    ,tt.HostName
    ,tt.LoginName
    ,tt.ApplicationName
    ,tt.LoginName
FROM sys.traces AS t
CROSS APPLY fn_trace_gettable(
    --get trace folder and add base file name log.trc
    REVERSE(SUBSTRING(REVERSE(t.path), CHARINDEX(N'\', REVERSE(t.path)), 128)) + 'log.trc', default) AS tt
JOIN sys.trace_events AS te ON
    te.trace_event_id = tt.EventClass
JOIN sys.trace_subclass_values AS tesv ON
    tesv.trace_event_id = tt.EventClass
    AND tesv.subclass_value = tt.EventSubClass
WHERE
    t.is_default = 1 --default trace
    AND tt.ObjectName = N'YourView'
    AND tt.DatabaseName = N'YourDatabase';

Note the default trace is a rollover trace that keeps a maximum of 100MB so it might not have the forensic info if the view was recreated a while ago.

Upvotes: 6

IVNSTN
IVNSTN

Reputation: 9334

Yes, this is a DDL trigger. Sample trigger text is included in MSDN article about this kind of triggers. I'd say such a trigger is a must on production database for auditing reasons.

https://technet.microsoft.com/en-us/library/ms187909.aspx

Another trick is to create dependent on this object (view) another object (view?) with SCHEMA_BINDING option. This will make impossible to drop any object schema-bound object depends on.

Upvotes: 1

Related Questions