04-28-14, 13:45 #1Registered User
- Join Date
- Apr 2014
Unanswered: Data being deleted by service account
I have an older instance of SQL Server (2000) used for an old installation of Great Plains. Over the years there have been several custom application built to interface various accounting related systems with the data on this server.
Recently there have been several instances where random transactions are being deleted. The folks in the accounting group setup Great Plains to track any deletes from the GL account and determine who did the deletes. They're telling me that the same account we use to launch SQL Server is the account deleting the data.
It's possible that someone knows the login creds for this account and is using it to do some task that's causing the problem, but that's a long shot in my opinion. I rather believe that one of these custom interface apps is causing the problem.
One of the custom interfaces includes several SQL jobs that run that do some truncating and bulk inserting. In each case these jobs are owned by the sa account. If one of these jobs deleted the data would it be reported as being deleted by sa or by the service account since that's the account that launches SQL Server Agent?
04-29-14, 10:23 #2Annie's Dog Walker
Provided Answers: 6
- Join Date
- Nov 2004
- on the wrong server
I have been in this boat but it was a multi-terabyte transactional monster with hundreds of SQL jobs, windows services, web apps and everything else hitting it. What you want to do is create what I call a snitch-trigger on the table in GP that will capture all of the inbound information it can on the user process and SQL doing the delete.
I fortunately do not have a SQL 2000 instance handy but I just dug an example out of my email I did for someone. I think everything should work here in 200 except maybe a couple of the functions in the INSERT INTO SELECT statement, and you can work with the SQL 2000 functions for workarounds.
IF exists(SELECT 1 FROM information_schema.tables WHERE table_name = 'SeansTest')
DROP TABLE dbo.SeansTest
CREATE TABLE dbo.SeansTest(id int identity(1,1) primary key clustered, number int)
IF exists(SELECT 1 FROM information_schema.tables WHERE table_name = 'SeansTest_Audit')
DROP TABLE dbo.SeansTest_Audit
CREATE TABLE dbo.SeansTest_Audit(
SeansTest_AuditID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
entereddate datetime default(getdate()))
DROP TRIGGER dbo.SeansTestTrigger
CREATE TRIGGER dbo.SeansTestTrigger ON dbo.Users
DECLARE @SQL nVARCHAR(4000) = ''
DECLARE @SPID INT = @@SPID
IF OBJECT_ID('tempDB..#temp') is not null DROP TABLE #temp
Create table #temp(EventType varchar(100),[Parameters] varchar(100),EventInfo varchar(7800))
SET @SQL = 'DBCC INPUTBUFFER(' + CAST(@SPID as nVarchar(100)) + ')'
INSERT INTO #temp
EXEC sp_executesql @SQL
INSERT INTO dbo.SeansTest_Audit(ID,
SELECT COALESCE(i.userid,d.userid) as id,i.password as new,d.password as old,@SPID,t.EventInfo,SYSTEM_USER,USER_NAME(),HOST _NAME()
FROM inserted i
FULL OUTER JOIN deleted d
on i.userid = d.userid
CROSS JOIN #temp t
WHERE COALESCE(i.password,'X') <> COALESCE(d.password,'X')
AND i.userid = 19
Update users SET password = 'password' WHERE userID = 19If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry. Earnest Hemingway, A Farewell To Arms.