Auditing SQL Server - who is changing what and when? SOX complaince

Question

In my organization, we are doing very limited logging or any sort to capture who is changing what and when.

I am seeking help here to understand what should be the best practices to capture any logging whatsoever happening in our SQL Server database.

I am thinking of going over the tables based on the important business uses cases that a user can perform with the application and then making an xl file with the following fields so that I keep this file as a reference for myself.

current XL file to start to capture initial changes

My question: is there any other better way to capture the current change in the database, and is there a way in SQL Server that I use to find out if we are capturing any logging in the database?

We don't have any CDC implementation or C2 audit tracing enables or change tacking enabled.

Management want's to leverage the data captured in the database tables.

You cannot really do proper logging from within the same system, it has to be another system that receives that data and stores it. Otherwise anyone can change the audit tables. — Charlieface, Mar 10 '21 at 20:29
you are correct, however, the goal is to at least start filling up the technical debt of having any logging whatsoever. If I could put a report that can bring up auditing from 0 to 20% while only capturing the data around the key business use case, I am on winning track then. So any help in that regard would be useful. — ishan sharma, Mar 10 '21 at 20:35

score 0 · Answer 1 · edited Mar 11 '21 at 02:39

I am working on a similar project, you can use below design, i am explaining with student subject example

CREATE TABLE [dbo].[AudRel](
    [AudId] [int] IDENTITY(1,1) NOT NULL,
    [AudTableName] [varchar](100) NULL,
    [AudFieldName] [varchar](100) NULL,
    [AudFieldID] [varchar](30) NULL,
 CONSTRAINT [PK_AuditRel] PRIMARY KEY CLUSTERED 
(
    [AudId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Student](
    [StudentID] [int] IDENTITY(1,1) NOT NULL,
    [StudentName] [varchar](100) NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
    [StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Student_Audit](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [StudentID] [int] NOT NULL,
    [StudentName] [varchar](100) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[StudentSubject](
    [SSID] [int] IDENTITY(1,1) NOT NULL,
    [StudentID] [int] NULL,
    [SubjectID] [int] NULL,
 CONSTRAINT [PK_StudentSubject] PRIMARY KEY CLUSTERED 
(
    [SSID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[StudentSubject_Audit](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SSID] [int] NOT NULL,
    [StudentID] [int] NULL,
    [SubjectID] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Subject](
    [SubjectID] [int] IDENTITY(1,1) NOT NULL,
    [SubjectName] [varchar](50) NULL,
 CONSTRAINT [PK_Subject] PRIMARY KEY CLUSTERED 
(
    [SubjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Subject_Audit](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SubjectID] [int] NOT NULL,
    [SubjectName] [varchar](50) NULL
) ON [PRIMARY]


SET IDENTITY_INSERT [dbo].[AudRel] ON 
INSERT [dbo].[AudRel] ([AudId], [AudTableName], [AudFieldName], [AudFieldID]) VALUES (1, N'Student', N'StudentName', N'StudentID')
INSERT [dbo].[AudRel] ([AudId], [AudTableName], [AudFieldName], [AudFieldID]) VALUES (2, N'Subject', N'SubjectName', N'SubjectID')


SET IDENTITY_INSERT [dbo].[AudRel] OFF

SET IDENTITY_INSERT [dbo].[Student] ON 
INSERT [dbo].[Student] ([StudentID], [StudentName]) VALUES (1, N'Alex')
INSERT [dbo].[Student] ([StudentID], [StudentName]) VALUES (2, N'DSouza')
SET IDENTITY_INSERT [dbo].[Student] OFF

SET IDENTITY_INSERT [dbo].[StudentSubject] ON 
INSERT [dbo].[StudentSubject] ([SSID], [StudentID], [SubjectID]) VALUES (1, 1, 1)
INSERT [dbo].[StudentSubject] ([SSID], [StudentID], [SubjectID]) VALUES (2, 2, 1)
INSERT [dbo].[StudentSubject] ([SSID], [StudentID], [SubjectID]) VALUES (3, 2, 2)
SET IDENTITY_INSERT [dbo].[StudentSubject] OFF

SET IDENTITY_INSERT [dbo].[Subject] ON 
INSERT [dbo].[Subject] ([SubjectID], [SubjectName]) VALUES (1, N'English')
INSERT [dbo].[Subject] ([SubjectID], [SubjectName]) VALUES (2, N'Mathematics')
SET IDENTITY_INSERT [dbo].[Subject] OFF

and then use below query to dynamically fetch fields have been changed. From the UI you need to pass the AudRelID

DECLARE @TableName VARCHAR(100),@FieldName VARCHAR(100),@FieldID VARCHAR(100)

SELECT @TableName = [AudTableName]
, @FieldName=[AudFieldName]
, @FieldID=[AudFieldID] 
FROM [dbo].[AudRel] WHERE [AudId] = 1 -- (Ex : StudentHistory)


DECLARE @SQL NVARCHAR(MAX) = N'
 SELECT ID,'  + @FieldID +
','  + @FieldName + ' FROM ' +  @TableName + '_Audit ' + ' WHERE ' + @FieldID + ' = ' 
+ Convert(varchar(20),@FieldID) 

print @SQL
EXECUTE sp_executesql @SQL

Thank you for sharing with me the details and the SQL code, however, my question is on the documentation side, of using best practices of finding the gaps in the current auditing of DML in the database. I am certain your code can help me implement the auditing around key business tables. — ishan sharma, Mar 12 '21 at 15:29

Auditing SQL Server - who is changing what and when? SOX complaince

1 Answers1