question

sirajahmed_sql avatar image
sirajahmed_sql asked

SQL Audit Logs - Who did the change

Hi SQL Techies, With SQL Server Audit Logs, **Can we find** user details who did the change or who run the query against database objects Regards Siraj Ahmed
sql-server-2008audit
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

eghetto avatar image
eghetto answered
You can ask your default trace, if you like: DECLARE @enable INT; SELECT TOP 1 @enable = CONVERT(INT, value_in_use) FROM sys.configurations WHERE name = 'default trace enabled'; IF @enable = 1 --default trace is enabled BEGIN DECLARE @curr_tracefilename VARCHAR(500); DECLARE @base_tracefilename VARCHAR(500); DECLARE @indx INT; SELECT @curr_tracefilename = PATH FROM sys.traces WHERE is_default = 1; SET @curr_tracefilename = REVERSE(@curr_tracefilename); SET @indx = PATINDEX('%\%',@curr_tracefilename) SET @curr_tracefilename = REVERSE(@curr_tracefilename); SET @base_tracefilename = LEFT(@curr_tracefilename,LEN(@curr_tracefilename) - @indx) + '\log.trc'; SELECT Servername AS InstanceName ,ObjectName ,DatabaseName ,StartTime ,CASE ObjectType WHEN 8259 THEN 'CHECK' WHEN 8260 THEN 'DEFAULT' WHEN 8262 THEN 'FK' WHEN 8272 THEN 'SP' WHEN 8276 THEN 'TRIGGER Server' WHEN 8277 THEN 'TABLE' WHEN 8278 THEN 'VIEW' WHEN 16964 THEN 'DATABASE' WHEN 17235 THEN 'SCHEMA' WHEN 17475 THEN 'LOGIN' WHEN 17985 THEN 'FUNCTION (CLR-Aggregate)' WHEN 17993 THEN 'FUNCTION Inline' WHEN 18004 THEN 'FUNCTION' WHEN 19280 THEN 'PK' WHEN 20038 THEN 'FUNCTION Scalar' WHEN 20821 THEN 'UNIQUE' WHEN 21057 THEN 'ROLE Application' WHEN 21075 THEN 'SERVER' WHEN 21076 THEN 'TRIGGER SQL' WHEN 21313 THEN 'ASSEMBLY' WHEN 21333 THEN 'USER' WHEN 21572 THEN 'TRIGGER Database' WHEN 22868 THEN 'TYPE' WHEN 17232 THEN 'CLR Procedure' WHEN 21574 THEN 'CLR Table Valued Function' WHEN 21318 THEN 'CLR Scalar Function' WHEN 8275 THEN 'System Table' WHEN 22601 THEN 'INDEX' ELSE CAST(ObjectType AS VARCHAR(12)) + ' - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.de/udb9/html/42f85c50-34c9-49ca-955f-af9595e2707f.htm' END AS ObjectTypeName ,LoginName ,ISNULL(ApplicationName, N'?') AS ApplicationName ,CASE EventClass WHEN 46 THEN 'CREATE' WHEN 47 THEN 'DROP' WHEN 164 THEN 'ALTER' WHEN 104 THEN 'SQL Login - SQL Login: ' + ISNULL(TargetLoginName, '?') + ', Action: ' + CASE EventSubClass WHEN 1 THEN 'ADD' WHEN 2 THEN 'DROP' ELSE '?' END WHEN 105 THEN 'Windows Login - Login: ' + ISNULL(TargetLoginName, '?') + ', Action: ' + CASE EventSubClass WHEN 1 THEN 'ADD' WHEN 2 THEN 'DROP' ELSE '?' END WHEN 108 THEN 'Srvrolemember - Role: ' + ISNULL(RoleName, '?') + ', Login: ' + ISNULL(TargetLoginName, '?') + ', Action: ' + CASE EventSubClass WHEN 1 THEN 'ADD' WHEN 2 THEN 'DROP' ELSE '?' END WHEN 109 THEN 'DBuser - User: ' + ISNULL(TargetUserName, '?') + ', Action: ' + CASE EventSubClass WHEN 1 THEN 'ADD' WHEN 2 THEN 'DROP' WHEN 3 THEN 'Grant database access' WHEN 4 THEN 'Revoke database access' ELSE '?' END WHEN 110 THEN 'DBRolemember - DB Role: ' + ISNULL(RoleName, '?') + ', DB User: ' + ISNULL(TargetUserName, '?') + ', Action: ' + CASE EventSubClass WHEN 1 THEN 'ADD' WHEN 2 THEN 'DROP' ELSE '?' END ELSE CAST(EventClass AS VARCHAR(3)) + ' - ?' END AS DDLoperation FROM ::fn_trace_gettable(@base_tracefilename,DEFAULT) WHERE ((EventClass IN (46, 47, 164) AND EventSubClass = 0) OR (EventClass IN (104, 105, 108, 109, 110))) AND (DatabaseName 'tempdb' OR DatabaseName IS NULL) AND (NOT ObjectType IN(21587) OR ObjectType IS NULL); -- 21587 = Statistic END
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image Jeff Moden commented ·
I haven't tried it, yet, but that looks incredibly useful and I'll give it a shot tomorrow. Thanks for sharing it.
0 Likes 0 ·
tlengyel avatar image
tlengyel answered
Instead of SQL Trace, SQL Server Audit can also be used, like the OP asked. With database-level auditing, you can monitor who updated or run other queries against your database objects. For this, you would need to create a server audit and a database audit specification: - [CREATE SERVER AUDIT][1] - [CREATE DATABASE AUDIT SPECIFICATION][2] - [Database Audit Specification][3] - [Database-level audit actions][4] [1]: http://msdn.microsoft.com/en-us/library/cc280448.aspx [2]: http://msdn.microsoft.com/en-us/library/cc280404.aspx [3]: https://www.ultimatewindowssecurity.com/sqlserver/auditpolicy/databaseauditspecification.aspx [4]: https://www.ultimatewindowssecurity.com/sqlserver/auditpolicy/auditactiongroups/database-level_audit_actions.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.