December 11, 2013 at 7:25 am
I work in a small company with 90 people. I have been working as a database developer but recently started my training as an admin. It my company's thing that they give 'support' as much as importance (or even more) they give to developers. So some of them tend to have production permissions. Every once in a while when a SQLServer Agent maintenance job fails, i noticed its really difficult and time taking to see the actual location of the failure. Most of the time, it is missing procedures, tables that are truncated. So some one from support or executives is doing that. Is there any way that i can track when some one actually drops an object from Production. I am not surprised if thats an in built thing in SQL server, as that's really important thing to be in SQL server. Even if its not there, i am wondering if we can do that with programming.. Any ideas?? Or models??? Or code??? 😉
--Pra:-):-)--------------------------------------------------------------------------------
December 11, 2013 at 7:31 am
Do you want to prevent them from dropping objects? If so, you could write a DML trigger to prevent that:http://technet.microsoft.com/en-us/library/ms191524(v=sql.105).aspx. Or you could use the trigger to write the action to a log table too. You could also use Extended Events to track what is happening.
December 11, 2013 at 8:52 am
might also be worth looking in the default trace to track some of those changes retrospectively
December 11, 2013 at 9:02 am
Keith Tate (12/11/2013)
Do you want to prevent them from dropping objects? If so, you could write a DML trigger to prevent that:http://technet.microsoft.com/en-us/library/ms191524(v=sql.105).aspx. Or you could use the trigger to write the action to a log table too. You could also use Extended Events to track what is happening.
I dont want to prevent anybody from dropping, as we ourselves drop and recreate the objects with improved code thrice a week. I know we can create triggers on delete update and insert. But i donno if we can create triggers on drop statement though. I would love study on Extended events though
--Pra:-):-)--------------------------------------------------------------------------------
December 11, 2013 at 9:03 am
adb2303 (12/11/2013)
might also be worth looking in the default trace to track some of those changes retrospectively
Default Trace???
--Pra:-):-)--------------------------------------------------------------------------------
December 11, 2013 at 9:21 am
Check out these resources:
Default Trace:https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/
December 11, 2013 at 11:52 am
For me, the default trace does not have enough information. I set up a server side trace that runs 24/7, and I keep the trace files for about 45 days. I use FORFILES in a weekly job to delete them older than 45 days.
When I need to investigate something, I have a script to dump specified trace files into a SQL table for easier viewing & selecting.
December 12, 2013 at 4:19 am
Default trace and a server-side trace are absolutely one direction for solving this.
But, a better direction would be to take advantage of extended events. They're more lightweight, less intrusive and you can extend better control over them. System_health, an extended event session, is running automatically on your system if you have 2008 or better. It collects just a little more information than the default trace, including deadlock graphs. I would pursue that as a means of setting up the auditing that you need.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 13, 2013 at 1:58 am
Keith Tate (12/11/2013)
Do you want to prevent them from dropping objects? If so, you could write a DML trigger to prevent that:http://technet.microsoft.com/en-us/library/ms191524(v=sql.105).aspx. Or you could use the trigger to write the action to a log table too. You could also use Extended Events to track what is happening.
Dropping objects is a DDL thing....
A trigger in this case would be a good idea I think placed ON_ALL_SERVER to monitor the instance.
This was my solution to at least monitor who performs what DDL and what was done.....
/****** Object: DdlTrigger [trg_DDLMonitor] Script Date: 12/13/2013 09:56:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [trg_DDLMonitor] on all server
for
DDL_DATABASE_LEVEL_EVENTS
as
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(max),
@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(MAX)')
IF
IS_MEMBER ('db_owner') = 0 or IS_MEMBER ('db_owner') = 1
BEGIN
PRINT('DDL action has been logged');
INSERT INTO Monitor.dbo.tbl_DDLMonitor(
servername,
DBName,
OSUserName,
SQLUser,
EventDate,
EventData)
VALUES
(@HOSTNAME,
@DATABASENAME,
@SYSTEMUSER,
@CURRENTUSER,
@EVENTDATE,
@eventData)
END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trg_DDLMonitor] ON ALL SERVER
GO
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply