Find who or when an SQL view is deleted in SQL Server

Question

One of our SQL Server databases has many SQL views. One kind of vision disappears 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 refused a view?

Alternatively, you can add a SQL Server trigger to the view command DROPfor capture and failure << 20>?

Answer 1

This information is written to the default trace. The following is an example query for 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';

, - , 100 , , .

Answer 2

, DDL. MSDN . , .

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

() (view?) SCHEMA_BINDING. .

Answer 3

, DDL DROP_VIEW. , - [HumanResources].[vEmployee] AdventureWorks. EVENTDATA() :

<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>

DDL:

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 = '[e-mail]', 
    @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

Answer 4

trigger , DDL :

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

EVENTDATA()

, , AuditDatabaseObject

, @Chris Pickford.