September 18, 2014 at 5:02 am
Every now and then (about once or twice a month - no real pattern to it either) a column drops off the view. I have created a DDL trigger which captures ALTER, CREATE and DROP but it didn't capture anything when the column dropped out yesterday but I can see my alter statement correcting it clearly, (and I can see alter, create and drop statements on other views that I know have occurred).
I've toyed with the idea that this is some kind of deliberate untoward behaviour, and that the DDL trigger was disabled while the view altered. Unfortunately they don't have Enterprise installed so I can't audit the disabling or enabling of the trigger. I've also enabled an identity column on the trigger output table so that I can see if anyone has deleted rows out of my auditing data.
I was wondering what might be causing the view to revert other than something that the DDL trigger should capture? It's frustrating as the column is crucial to the running of a report the customer needs. I can write a scheduled agent script to recreate the view, of course, but it would be nice to be able to pin down exactly what is going on?
Any ideas greatly appreciated 🙂
----------------------------------
--my trigger output table:
USE [Company1]
GO
/****** Object: Table [dbo].[bespoke_audit_ddl] Script Date: 09/18/2014 11:53:45 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[bespoke_audit_ddl]') AND type in (N'U'))
DROP TABLE [dbo].[bespoke_audit_ddl]
GO
USE [Company1]
GO
/****** Object: Table [dbo].[bespoke_audit_ddl] Script Date: 09/18/2014 11:53:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[bespoke_audit_ddl](
[eventId] [int] IDENTITY(1,1) NOT NULL,
[EventTime] [datetime] NULL,
[LoginName] [sysname] NOT NULL,
[UserName] [sysname] NOT NULL,
[DatabaseName] [sysname] NOT NULL,
[SchemaName] [sysname] NOT NULL,
[ObjectName] [sysname] NOT NULL,
[ObjectType] [varchar](50) NULL,
[DDLCommand] [varchar](max) NULL,
CONSTRAINT [PK_bespoke_audit_dll_eventId] PRIMARY KEY NONCLUSTERED
(
[eventId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
------------------------------------------
--my trigger
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'company1_view_audit')
DISABLE TRIGGER [company1_view_audit] ON DATABASE
GO
USE [Company1]
GO
/****** Object: DdlTrigger [company1_view_audit] Script Date: 09/18/2014 11:55:49 ******/
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'company1_view_audit')DROP TRIGGER [company1_view_audit] ON DATABASE
GO
USE [Company1]
GO
/****** Object: DdlTrigger [company1_view_audit] Script Date: 09/18/2014 11:55:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [company1_view_audit]
ON database
FOR CREATE_VIEW, ALTER_VIEW, DROP_VIEW, CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER, ALTER_TABLE
AS
DECLARE @eventInfo XML
SET @eventInfo = EVENTDATA()
INSERT INTO bespoke_audit_ddl VALUES
(
REPLACE(CONVERT(VARCHAR(50),
@eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
CONVERT(SYSNAME,
@eventInfo.query('data(/EVENT_INSTANCE/LoginName)')),
CONVERT(SYSNAME,
@eventInfo.query('data(/EVENT_INSTANCE/UserName)')),
CONVERT(SYSNAME,
@eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')),
CONVERT(SYSNAME,
@eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')),
CONVERT(SYSNAME,
@eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')),
CONVERT(VARCHAR(50),
@eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')),
CONVERT(VARCHAR(MAX),
@eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
DISABLE TRIGGER [company1_view_audit] ON DATABASE
GO
ENABLE TRIGGER [company1_view_audit] ON DATABASE
GO
September 18, 2014 at 5:08 am
Have you tried looking in the default trace?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 18, 2014 at 7:59 am
Yes - disable/enable trigger are not in the default trace unfortunately 🙁
September 18, 2014 at 9:19 am
What about revoking permissions to everyone but you on the trigger?
Another option is setting up a database mail alert for changes on the view, so that you're notified right away, even if somebody deletes the auditing data you collect.
-- Gianluca Sartori
September 19, 2014 at 1:38 am
unfortunately it could be someone who logs in under the same credentials as me.
The db mail idea is good, although in the same way as disabling the trigger, db mail could be disabled while it is done I suppose.
Does anyone have any opinion on if there is anything else that could be reverting the view other than the DDL statements I should be capturing with the audit?
September 19, 2014 at 5:06 am
phingers (9/19/2014)
unfortunately it could be someone who logs in under the same credentials as me.The db mail idea is good, although in the same way as disabling the trigger, db mail could be disabled while it is done I suppose.
Does anyone have any opinion on if there is anything else that could be reverting the view other than the DDL statements I should be capturing with the audit?
well you've identified a core problem: multiple users using the same login (sharing sa or some other sysadmin)
the right thing to do is obvious: create windows logins for each user, disable that login and change the password for the one that was being shared, and tell everyone it's because security compliance requires it...blame it on those damn auditors or something. Institutional Inertia, where everyone has always been doing it a certain way, is probably what is hurting you.
the second you put that in place, your ability to point out who did what will automatically resolve.
you could also do the obvious, and tell everyone to stop fiddling with the view.
Lowell
September 19, 2014 at 6:13 am
I completely agree, but I the support company I work for have only one login to this customer, and they will not create separate ones for each user unfortunately.
Any thoughts on whether the view can change without a DDL statement? I'd just like to sense check my auditing 'should' capture it.
September 19, 2014 at 7:58 am
phingers (9/19/2014)
I completely agree, but I the support company I work for have only one login to this customer, and they will not create separate ones for each user unfortunately.Any thoughts on whether the view can change without a DDL statement? I'd just like to sense check my auditing 'should' capture it.
No. A view cannot change without DDL. Just like a table or any other object can't change without some DDL that makes it change.
_______________________________________________________________
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/
September 19, 2014 at 9:24 am
Does the view use "SELECT *" and/or some other generic column name, such as $ROWGUID? [If you posted the view definition, sorry, I didn't see it.]
You could also create the view with SCHEMABINDING, assuming the view meets the restrictions for that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 19, 2014 at 9:33 am
no generic columns - here is the view
CREATE VIEW [dbo].[bespoke_packaging_per_job]
AS
SELECT DISTINCT dbo.jobbom.jobnum, dbo.lotdet.lotnum, dbo.prdall.descr, dbo.spdfil.mark, dbo.boming.slot
FROM dbo.bomlot WITH (nolock) INNER JOIN
dbo.lotdet WITH (nolock) ON dbo.bomlot.lotdetid = dbo.lotdet.lotdetid INNER JOIN
dbo.spdfil WITH (nolock) ON dbo.lotdet.UOMStockingProdnum = dbo.spdfil.prodnum INNER JOIN
dbo.prdall WITH (nolock) ON dbo.spdfil.mascode = dbo.prdall.mascode INNER JOIN
dbo.boming WITH (nolock) ON dbo.bomlot.bomingid = dbo.boming.bomingid INNER JOIN
dbo.jobbom WITH (nolock) ON dbo.boming.jobbomid = dbo.jobbom.jobbomid
WHERE (dbo.bomlot.uomactqty > 0) AND (dbo.spdfil.ProductType = 2)
September 19, 2014 at 9:41 am
That "DISTINCT" is a big concern. Either the joins are producing dups, and thus the joins need modified, or the code is needlessly forcing SQL to sort the results.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 19, 2014 at 9:42 am
phingers (9/19/2014)
no generic columns - here is the view
CREATE VIEW [dbo].[bespoke_packaging_per_job]
AS
SELECT DISTINCT dbo.jobbom.jobnum, dbo.lotdet.lotnum, dbo.prdall.descr, dbo.spdfil.mark, dbo.boming.slot
FROM dbo.bomlot WITH (nolock) INNER JOIN
dbo.lotdet WITH (nolock) ON dbo.bomlot.lotdetid = dbo.lotdet.lotdetid INNER JOIN
dbo.spdfil WITH (nolock) ON dbo.lotdet.UOMStockingProdnum = dbo.spdfil.prodnum INNER JOIN
dbo.prdall WITH (nolock) ON dbo.spdfil.mascode = dbo.prdall.mascode INNER JOIN
dbo.boming WITH (nolock) ON dbo.bomlot.bomingid = dbo.boming.bomingid INNER JOIN
dbo.jobbom WITH (nolock) ON dbo.boming.jobbomid = dbo.jobbom.jobbomid
WHERE (dbo.bomlot.uomactqty > 0) AND (dbo.spdfil.ProductType = 2)
Why all the NOLOCK hints? Is accuracy not important for this view? You can and will get missing and/or duplicate rows using this hint. It might be ok but you need to make certain you understand that hint.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
I don't see anything in your view that would cause erratic behavior. Somebody or some process is running DDL statements to alter your view.
_______________________________________________________________
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/
September 19, 2014 at 1:56 pm
Can't you get the HostName from a trace of who is running alter view statements on that view? Then possibly track down the actual physical computer doing it?
Or as an extremely dumb solution just schedule a job to run every however often that changes the view to whatever it's supposed to be then see who complains when their alter view statement gets rolled back right after they run it?
September 20, 2014 at 12:24 pm
Thanks Sean, interesting about use of the nolock hint, I wasn't aware of that and this is recommedned by the software vendors - I will discuss it with them what their reason might be.
September 20, 2014 at 12:52 pm
Thanks ZZartin, I could run a trace as you say but I suspect that this is deliberate and malicious behaviour and as such whoever is doing it is good at covering their steps, therefore an agent job to rectify their breaking it will be a workaround, but it won't flush anyone out.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply