Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2014
    Posts
    1

    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?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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')
    BEGIN
    DROP TABLE dbo.SeansTest
    END
    GO
    CREATE TABLE dbo.SeansTest(id int identity(1,1) primary key clustered, number int)
    GO
    IF exists(SELECT 1 FROM information_schema.tables WHERE table_name = 'SeansTest_Audit')
    BEGIN
    DROP TABLE dbo.SeansTest_Audit
    END
    GO
    CREATE TABLE dbo.SeansTest_Audit(
    SeansTest_AuditID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    ID int,
    oldpassword varchar(100),
    newpassword varchar(100),
    spid int,
    EventInfo varchar(3500),
    sys_user varchar(100),
    username varchar(100),
    hostname varchar(100),
    entereddate datetime default(getdate()))

    GO
    DROP TRIGGER dbo.SeansTestTrigger
    GO
    CREATE TRIGGER dbo.SeansTestTrigger ON dbo.Users
    AFTER UPDATE
    AS
    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,
    newpassword,
    oldpassword,
    spid,
    EventInfo,
    sys_user,
    username,
    hostname)
    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

    GO
    Update users SET password = 'password' WHERE userID = 19
    “If 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •