August 22, 2013 at 11:54 am
Hello all,
Is there a way to determine who deleted a view?
Thanks
August 22, 2013 at 12:07 pm
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/
August 22, 2013 at 12:27 pm
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?
August 22, 2013 at 1:11 pm
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/
August 26, 2013 at 12:36 am
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.
August 28, 2013 at 9:10 am
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
August 28, 2013 at 10:56 am
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?
August 28, 2013 at 11:13 am
SQL Surfer '66 (8/26/2013)
Sean Lange (8/22/2013)
In SSMS right click the database -> Reports -> Schema Changes HistoryThanks 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!
August 28, 2013 at 2:15 pm
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
August 28, 2013 at 2:20 pm
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!! 🙂
August 28, 2013 at 2:43 pm
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