October 5, 2018 at 8:49 am
Just an FYI: I don't need code. I just need thoughts. This is me spit-balling.
I need to come up with a way to monitor user activity on a new database that includes NTID, the object being accessed, the action being done (SELECT, CREATE, EXECUTE, etc.) and a timestamp. I'm pretty sure the way I need to go is a database trigger, but just to cover my bases and make sure I'm not ignoring a less-resource intensive possibility...
Does anyone have additional thoughts on what other SQL properties I could use to monitor these things? Aside from a persistent Profiler trace or a hamster with its eyes wired open as it watches Activity Monitor 24/7, that is.
October 5, 2018 at 8:57 am
SQL Audit? You may need Enterprise Edition to do database-level auditing in 2012, though.
John
October 5, 2018 at 9:13 am
SQL Audit will do this if you need this to be accurate and secure. Someone can turn it off, but that will be logged as well. However, as mentioned EE.
if you just want this for tracking to help determine what's happened, Extended Events will do this.
October 8, 2018 at 5:36 am
Thanks, Guys. I didn't even think of EE or SQL Audit. I'll look at both of those.
October 8, 2018 at 6:28 am
BAH. SQL Audit (which looks like it could be the right solution) doesn't output to SQL tables. And the people who would be consuming the results of this auditing aren't DBAs or Server Admin. So they can't get access to the local SANs or the logs.
So I'm stuck with either triggers or XEs, the later of which is going to require a learning curve to only get what I want without extraneous information the users don't need or won't understand.
Bleargh.
October 8, 2018 at 6:47 am
Just set up a job that imports the data from the audit file every 15 minutes or whatever interval is suitable for you. You can have this for free:
ALTER PROCEDURE AuditImport
@DaystoKeep smallint = 42
, @Debug bit = 0 -- 0 to suppress information helpful when debugging
, @ExcludeServiceAccount bit = 1
AS
-- AuditImport
-- John Mitchell 2018-06-13
-- Imports unimported events from the audit log files in the
-- log folder into the AuditTable table. Each different load
-- is given its own number, starting at -2,147,483,648 and
-- increasing by 1 each time. We import events up to two seconds
-- ago so that events that happened at the same time don't get
-- split across two different LoadIDs. Also deletes entries from
-- the same table that are more than @DaystoKeep days old
-- @ExcludeServiceAccount parameter: excludes any action performed
-- in the context of the SQL Server Agent service account. This
-- should always be left at its default of 1: the only reason
-- it's there is for testing on an instance where the service
-- runs under the same account that's doing the testing
DECLARE
@LoadID int
, @LogLocation varchar(400) = CAST(SERVERPROPERTY('ErrorLogFileName') AS varchar(400))
, @MostRecentEntryinTable datetime2
, @SQLServerAccount sysname
, @SQLServerAgentAccount sysname;
DECLARE @DebugTable table (
InsertorDelete char(6) NOT NULL
, LoadID int NULL
, EventTime datetime2(7) NULL
, SeqNo int NULL
, ActionID varchar(4) NULL
, Succeeded bit NULL
, ClassType varchar(2) NULL
, ServerPrincipalName sysname NOT NULL
, DatabasePrincipalName sysname NOT NULL
, DBName sysname NOT NULL
, SchemaName sysname NOT NULL
, ObjectName sysname NOT NULL
, SQLStatement nvarchar(4000) NULL
, AdditionalInfo nvarchar(4000) NULL
);
IF @Debug = 1
SET NOCOUNT OFF
ELSE SET NOCOUNT ON;
SELECT @LoadID = COALESCE(MAX(LoadID) + 1,-2147483648)
FROM AuditTable;
IF @Debug = 1
SELECT @LoadID AS Valueof@LoadID;
-- Chop off file name to leave only path
SET @LogLocation = LEFT(@LogLocation,LEN(@LogLocation)-CHARINDEX('\',REVERSE(@LogLocation)));
SET @LogLocation = @LogLocation + '\MyAudit*.sqlaudit';
IF @Debug = 1
SELECT @LogLocation AS Valueof@LogLocation;
SELECT @MostRecentEntryinTable = COALESCE(MAX(EventTime),'19000101')
FROM BT.SQLAudit;
IF @Debug = 1
SELECT @MostRecentEntryinTable AS Valueof@MostRecentEntryinTable;
-- Get service accounts
SELECT @SQLServerAccount = service_account
FROM sys.dm_server_services
WHERE servicename = N'SQL Server (' + COALESCE(CAST(SERVERPROPERTY('InstanceName') AS sysname),N'MSSQLSERVER') + N')';
SELECT @SQLServerAgentAccount = service_account
FROM sys.dm_server_services
WHERE servicename = N'SQL Server Agent (' + COALESCE(CAST(SERVERPROPERTY('InstanceName') AS sysname),N'MSSQLSERVER') + N')';
IF @Debug = 1
SELECT
@SQLServerAccount AS Valueof@SQLServerAccount
, @SQLServerAgentAccount AS Valueof@SQLServerAgentAccount;
INSERT INTO AuditTable (
LoadID
, EventTime
, SeqNo
, ActionID
, Succeeded
, ClassType
, ServerPrincipalName
, DatabasePrincipalName
, DBName
, SchemaName
, ObjectName
, SQLStatement
, AdditionalInfo
)
OUTPUT 'Insert', Inserted.*
INTO @DebugTable
SELECT
@LoadID
, event_time
, sequence_number
, action_id
, succeeded
, class_type
, server_principal_name
, database_principal_name
, database_name
, schema_name
, object_name
, statement
, additional_information
FROM fn_get_audit_file(@LogLocation,default,default)
WHERE event_time > @MostRecentEntryinTable
AND event_time < DATEADD(SECOND,-2,CURRENT_TIMESTAMP)
-- The clauses below are necessary even though the audit itself is set up
-- with a very similar filter. This is because audit filters aren't
-- available in 2008 and so we have to filter in the SELECT instead
AND action_id NOT LIKE 'V[SD]ST' -- view server or database state
AND server_principal_name <> CASE @ExcludeServiceAccount
WHEN 1 THEN @SQLServerAgentAccount
ELSE ''
END
AND NOT (server_principal_name = @SQLServerAgentAccount AND statement LIKE 'ALTER INDEX%')
AND NOT (server_principal_name = @SQLServerAgentAccount AND action_id = 'BA'); -- backup
DELETE FROM AuditTable
OUTPUT 'Delete', Deleted.*
INTO @DebugTable
WHERE EventTime < DATEADD(DAY,-@DaystoKeep,CURRENT_TIMESTAMP);
IF @Debug = 1
SELECT * FROM @DebugTable;
John
October 8, 2018 at 6:57 am
John Mitchell-245523 - Monday, October 8, 2018 6:47 AMJust set up a job that imports the data from the audit file every 15 minutes or whatever interval is suitable for you. You can have this for free:
ALTER PROCEDURE AuditImport
@DaystoKeep smallint = 42
, @Debug bit = 0 -- 0 to suppress information helpful when debugging
, @ExcludeServiceAccount bit = 1
AS-- AuditImport
-- John Mitchell 2018-06-13
-- Imports unimported events from the audit log files in the
-- log folder into the AuditTable table. Each different load
-- is given its own number, starting at -2,147,483,648 and
-- increasing by 1 each time. We import events up to two seconds
-- ago so that events that happened at the same time don't get
-- split across two different LoadIDs. Also deletes entries from
-- the same table that are more than @DaystoKeep days old
-- @ExcludeServiceAccount parameter: excludes any action performed
-- in the context of the SQL Server Agent service account. This
-- should always be left at its default of 1: the only reason
-- it's there is for testing on an instance where the service
-- runs under the same account that's doing the testingDECLARE
@LoadID int
, @LogLocation varchar(400) = CAST(SERVERPROPERTY('ErrorLogFileName') AS varchar(400))
, @MostRecentEntryinTable datetime2
, @SQLServerAccount sysname
, @SQLServerAgentAccount sysname;
DECLARE @DebugTable table (
InsertorDelete char(6) NOT NULL
, LoadID int NULL
, EventTime datetime2(7) NULL
, SeqNo int NULL
, ActionID varchar(4) NULL
, Succeeded bit NULL
, ClassType varchar(2) NULL
, ServerPrincipalName sysname NOT NULL
, DatabasePrincipalName sysname NOT NULL
, DBName sysname NOT NULL
, SchemaName sysname NOT NULL
, ObjectName sysname NOT NULL
, SQLStatement nvarchar(4000) NULL
, AdditionalInfo nvarchar(4000) NULL
);IF @Debug = 1
SET NOCOUNT OFF
ELSE SET NOCOUNT ON;SELECT @LoadID = COALESCE(MAX(LoadID) + 1,-2147483648)
FROM AuditTable;IF @Debug = 1
SELECT @LoadID AS Valueof@LoadID;-- Chop off file name to leave only path
SET @LogLocation = LEFT(@LogLocation,LEN(@LogLocation)-CHARINDEX('\',REVERSE(@LogLocation)));
SET @LogLocation = @LogLocation + '\MyAudit*.sqlaudit';IF @Debug = 1
SELECT @LogLocation AS Valueof@LogLocation;SELECT @MostRecentEntryinTable = COALESCE(MAX(EventTime),'19000101')
FROM BT.SQLAudit;IF @Debug = 1
SELECT @MostRecentEntryinTable AS Valueof@MostRecentEntryinTable;-- Get service accounts
SELECT @SQLServerAccount = service_account
FROM sys.dm_server_services
WHERE servicename = N'SQL Server (' + COALESCE(CAST(SERVERPROPERTY('InstanceName') AS sysname),N'MSSQLSERVER') + N')';
SELECT @SQLServerAgentAccount = service_account
FROM sys.dm_server_services
WHERE servicename = N'SQL Server Agent (' + COALESCE(CAST(SERVERPROPERTY('InstanceName') AS sysname),N'MSSQLSERVER') + N')';IF @Debug = 1
SELECT
@SQLServerAccount AS Valueof@SQLServerAccount
, @SQLServerAgentAccount AS Valueof@SQLServerAgentAccount;INSERT INTO AuditTable (
LoadID
, EventTime
, SeqNo
, ActionID
, Succeeded
, ClassType
, ServerPrincipalName
, DatabasePrincipalName
, DBName
, SchemaName
, ObjectName
, SQLStatement
, AdditionalInfo
)
OUTPUT 'Insert', Inserted.*
INTO @DebugTable
SELECT
@LoadID
, event_time
, sequence_number
, action_id
, succeeded
, class_type
, server_principal_name
, database_principal_name
, database_name
, schema_name
, object_name
, statement
, additional_information
FROM fn_get_audit_file(@LogLocation,default,default)
WHERE event_time > @MostRecentEntryinTable
AND event_time < DATEADD(SECOND,-2,CURRENT_TIMESTAMP)
-- The clauses below are necessary even though the audit itself is set up
-- with a very similar filter. This is because audit filters aren't
-- available in 2008 and so we have to filter in the SELECT instead
AND action_id NOT LIKE 'V[SD]ST' -- view server or database state
AND server_principal_name <> CASE @ExcludeServiceAccount
WHEN 1 THEN @SQLServerAgentAccount
ELSE ''
END
AND NOT (server_principal_name = @SQLServerAgentAccount AND statement LIKE 'ALTER INDEX%')
AND NOT (server_principal_name = @SQLServerAgentAccount AND action_id = 'BA'); -- backupDELETE FROM AuditTable
OUTPUT 'Delete', Deleted.*
INTO @DebugTable
WHERE EventTime < DATEADD(DAY,-@DaystoKeep,CURRENT_TIMESTAMP);IF @Debug = 1
SELECT * FROM @DebugTable;John
John, you are WONDERFUL!
Thank you for this.
October 8, 2018 at 8:25 am
Quick question, John. What is this line of code supposed to be referencing?
SELECT @MostRecentEntryinTable = COALESCE(MAX(EventTime),'19000101')
FROM BT.SQLAudit;
The "FROM BT.SQLAudit" is confusing me. Is there another table I'm supposed to create in addition to AuditTable? Is this the file name?
October 8, 2018 at 8:30 am
Sorry, yes, that's the AuditTable table. I tried to soften the object names and make them more generic sounding, but clearly I missed one line out!
John
October 8, 2018 at 8:40 am
John Mitchell-245523 - Monday, October 8, 2018 8:30 AMSorry, yes, that's the AuditTable table. I tried to soften the object names and make them more generic sounding, but clearly I missed one line out!John
Thank you for clarifying. At least you know I was reading your code before implementing. @=)
October 16, 2018 at 11:09 am
Steve, Extended event is one option I'm going to test after I try the audit trigger that will have low impact on database performance.
thanks.
October 16, 2018 at 11:11 am
my understanding on deploying John's Solution.
Step 2: Create sql server agent job that will run when needed to know table updates
SELECT @MostRecentEntryinTable = COALESCE(MAX(EventTime),'19000101')
FROM BT.SQLAudit;
October 16, 2018 at 11:34 am
SQL learner22 - Tuesday, October 16, 2018 11:11 AMmy understanding on deploying John's Solution.Step 1: Create the AuditImport procedure on the database that tables that need monitoring are in.Step 2: Create sql server agent job that will run when needed to know table updates
SELECT @MostRecentEntryinTable = COALESCE(MAX(EventTime),'19000101')
FROM BT.SQLAudit;
Not quite. You have to set up SQL Audit first before you can implement John's solution, which just copies information from the log file into a table.
Google SQL Audit. Also, remember this has to be set up in 2 spots. Open up SSMS and go to Security -> Audits then right click and create New Audit. After that, go to the database level DatabaseName -> Security -> Database Audit Specifications. Use the name of the Audit you created on the server level to create the new specification under the database.
Then implement John's step, making sure both audit levels are enabled before you expect data in the table.
October 16, 2018 at 11:36 am
And you'd create the AuditImport procedure in the database where you want the data to be imported to, not where the tables being audited are (shouldn't be the same place, as you don't want people having enough rights to remove audits of their actions)
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
October 16, 2018 at 11:39 am
For future readers of this thread, I recommended it to SQLLearner22 on this other thread: https://www.sqlservercentral.com/Forums/2002068/SQL-server-Audit-Trigger-for-INSERT-UPDATE-DELETE
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply