Deteremine who deleted a view

  • Hello all,

    Is there a way to determine who deleted a view?

    Thanks

  • elee1969 (8/22/2013)


    Hello all,

    Is there a way to determine who deleted a view?

    Thanks

    If not too much time has elapsed you might still find it in the default trace.

    In SSMS right click the database -> Reports -> Schema Changes History

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (8/22/2013)


    elee1969 (8/22/2013)


    Hello all,

    Is there a way to determine who deleted a view?

    Thanks

    If not too much time has elapsed you might still find it in the default trace.

    In SSMS right click the database -> Reports -> Schema Changes History

    Thanks Sean!! That worked. Is there a way to set it to look for one day?

  • This page explains the default trace. If this is something you want or need in the future you probably should look into extended events.

    http://technet.microsoft.com/en-us/library/ms175513.aspx

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (8/22/2013)


    In SSMS right click the database -> Reports -> Schema Changes History

    Thanks Sean. Very interesting. Must take a closer look at the different reports.

  • I've created an automated process that logs all the CREATE, DROP, ALTER events from the default trace file into a centrally stored table (basically using a stored-procedure and a SQL Agent job). If you're interested in the code, I'll gladly post it/send to you

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • MyDoggieJessie (8/28/2013)


    I've created an automated process that logs all the CREATE, DROP, ALTER events from the default trace file into a centrally stored table (basically using a stored-procedure and a SQL Agent job). If you're interested in the code, I'll gladly post it/send to you

    Hey, Can you please post the script here?

  • SQL Surfer '66 (8/26/2013)


    Sean Lange (8/22/2013)


    In SSMS right click the database -> Reports -> Schema Changes History

    Thanks Sean. Very interesting. Must take a closer look at the different reports.

    I did not know these were here either. Some of them look pretty interesting at first glance. I'll have to see if they can be helpful in some everyday tasks. +1 on the thanks Sean!

  • I can't post the code from the office due to our proxy server not liking some of the keywords in the sql code, I can either email it to you or post it later this evening when I get home 🙂 Let me know.

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • MyDoggieJessie (8/28/2013)


    I can't post the code from the office due to our proxy server not liking some of the keywords in the sql code, I can either email it to you or post it later this evening when I get home 🙂 Let me know.

    Please post. Thanks!! 🙂

  • I think I got around it...pasted it from my iPhone!

    I've set this up with the central logging table on a database server, and have all other (desired) SQL Servers having a linked server to this box, so I can get all of the trace file entries in one place (great for SSRS reporting)

    Code to create the table:

    CREATE TABLE [dbo].[DBSchemaChangeMonitor](

    [RecID] [bigint] IDENTITY(1,1) NOT NULL,

    [Captured] [datetime] NOT NULL,

    [Server] [nchar](256) NOT NULL,

    [DBName] [nchar](256) NULL,

    [Command] [nchar](50) NOT NULL,

    [Application] [nchar](500) NULL,

    [spid] [int] NOT NULL,

    [Object] [nchar](500) NULL,

    [Login] [nchar](256) NULL,

    [ClientProcessID] [int] NULL,

    [WorkStation] [nchar](256) NULL,

    [InsertedOn] [date] NULL,

    CONSTRAINT [PK_DBSchemaChangeMonitor] PRIMARY KEY CLUSTERED

    (

    [RecID] ASC,

    [Captured] ASC,

    [Server] ASC,

    [Command] ASC,

    [spid] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    The code the the procedure itself - NOTE you will need to specify the paths to your default sql trace file (this is usually located in the default installation path of where you installed SQL Server)USE [YourDatabase]

    CREATE PROCEDURE [dbo].[dba_TrackSchemaChanges] ( @server sysname )AS

    /*

    ----------------------------------------------------------------------------------------------------------------

    Purpose : Tracks Schema changes across all DB's, logs to a central table in YourDatabase on YourServer

    Department :

    Created For : MyDoggieJessie

    ----------------------------------------------------------------------------------------------------------------

    NOTES :

    ----------------------------------------------------------------------------------------------------------------

    EXEC dbo.dba_TrackSchemaChanges

    */

    --DECLARE VARIABLES

    BEGIN

    DECLARE @d1 datetime

    DECLARE @diff int

    DECLARE @curr_tracefilename varchar(500)

    DECLARE @base_tracefilename varchar(500)

    DECLARE @indx int

    DECLARE @sql varchar(750)

    DECLARE @Cnt int

    END

    --SET VARIABLES

    BEGIN

    /* Check default trace file path - depending on the installation path for SQL you may want to add IF statements */

    IF @server = 'YourServer'

    SELECT

    @curr_tracefilename = 'E:\SQL\DATA\MSSQL10.DEV\MSSQL\LOG\'

    ELSE

    SELECT

    @curr_tracefilename = PATH

    FROM

    sys.traces

    WHERE

    is_default = 1

    SET @curr_tracefilename = REVERSE(@curr_tracefilename)

    SELECT

    @indx = PATINDEX('%\%', @curr_tracefilename)

    SET @curr_tracefilename = REVERSE(@curr_tracefilename)

    SET @base_tracefilename = LEFT(@curr_tracefilename, LEN(@curr_tracefilename) - @indx) + '\log.trc'

    END

    --CREATE TEMP TABLE

    BEGIN

    DECLARE @TmpTrace TABLE (

    obj_name nvarchar(256) COLLATE database_default,

    database_name nvarchar(256) COLLATE database_default,

    start_time datetime,

    event_class int,

    event_subclass int,

    object_type int,

    server_name nvarchar(256) COLLATE database_default,

    login_name nvarchar(256) COLLATE database_default,

    application_name nvarchar(256) COLLATE database_default,

    ddl_operation nvarchar(40) COLLATE database_default,

    spid int,

    clipid int,

    host nvarchar(40) COLLATE database_default)

    END

    /* ######################################### START MAIN PROCEDURE HERE ########################################## */

    BEGIN

    INSERT INTO @TmpTrace

    SELECT

    ObjectName,

    DatabaseName,

    StartTime,

    EventClass,

    EventSubClass,

    ObjectType,

    ServerName,

    LoginName,

    ApplicationName,

    'temp',

    spid,

    ClientProcessID,

    HostName

    FROM

    ::

    FN_TRACE_GETTABLE(@base_tracefilename, DEFAULT)

    WHERE

    objecttype NOT IN (21587)

    AND EventClass IN (46, 47, 164)

    AND EventSubclass = 0

    AND LoginName NOT IN ('NT AUTHORITY\NETWORK SERVICE', 'sa')

    AND DatabaseID <> 2

    AND StartTime NOT IN (SELECT

    Captured

    FROM

    [YourLinkedServer].YourDatabase.dbo.DBSchemaChangeMonitor)

    SET @Cnt = @@ROWCOUNT

    /* Process Records */

    IF @Cnt > 0

    BEGIN

    /* Update events to be understandable */

    UPDATE

    @TmpTrace

    SET

    ddl_operation = 'CREATE'

    WHERE

    event_class = 46

    UPDATE

    @TmpTrace

    SET

    ddl_operation = 'DROP'

    WHERE

    event_class = 47

    UPDATE

    @TmpTrace

    SET

    ddl_operation = 'ALTER'

    WHERE

    event_class = 164

    /* Fetch the Results */

    INSERT INTO [YourLinkedServer].YourDatabase.dbo.DBSchemaChangeMonitor

    ([Captured],

    [Server],

    [DBName],

    [Command],

    [Application],

    [spid],

    [Object],

    [Login],

    [ClientProcessID],

    [WorkStation],

    [InsertedOn])

    SELECT

    start_time,

    server_name,

    database_name,

    ddl_operation,

    '[' + CAST(object_type AS varchar(6)) + '] ' + application_name,

    spid,

    obj_name,

    login_name,

    clipid,

    host,

    CONVERT(varchar(10), GETDATE(), 111)

    FROM

    @TmpTrace

    WHERE

    --object_type not in (21587) -->> This is Table Statistics (not needed)

    obj_name NOT IN ('abc')

    ORDER BY

    server_name,

    start_time DESC

    END

    END

    /* ########################################## END MAIN PROCEDURE HERE ########################################### */Then just create a SQL Agent job that excecutesEXEC dbo.dba_TrackSchemaChanges @@SERVERNAME

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply