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.

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.

Answer 1

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