February 18, 2014 at 6:00 pm
Hi Experts ,
I am trying to audit a Truncate Table using SQL login. I did set up SQL auditing and was able to capture Domain\useid (windows authentication) user id when it was used to truncate a table. Now I am using a SQL login and doing same thing but SQL audit is not capturing anything. Is there any way I can capture a Truncate table command using SQL login ?
Thanks all in advance.
February 18, 2014 at 7:36 pm
Can you post the definition of your sql audit that is failing with the sql login?
You can script it and post that script.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 18, 2014 at 9:58 pm
This is on database
USE [TraceDB]
GO
CREATE DATABASE AUDIT SPECIFICATION [Truncate_DeleteAudit]
FOR SERVER AUDIT [Truncate_DeleteAudit]
ADD (DELETE ON DATABASE::[TraceDB] BY [dbo])
WITH (STATE = ON)
GO
this on master database
USE [master]
GO
/****** Object: Audit [Truncate_DeleteAudit] Script Date: 02/18/2014 20:58:22 ******/
CREATE SERVER AUDIT [Truncate_DeleteAudit]
TO FILE
(FILEPATH = N'G:\Audit_Logs\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
(QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = 'aa3ba548-4d34-42d2-818c-c40ef3992c73'
)
GO
February 18, 2014 at 10:03 pm
This is my uset.
CREATE LOGIN [testuser] WITH PASSWORD=N'ô8îlDùì+îí< ¥XÒ³#µë!©Eõý¬7>?', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [testuser] DISABLE
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply