DBAtlas · Database operations atlas

Create a ledger table, read its change history and verify integrity

SQL ServerSecurity

Command

USE [Musteri];
-- Ledger verification requires snapshot isolation:
ALTER DATABASE [Musteri] SET ALLOW_SNAPSHOT_ISOLATION ON;

-- Updatable ledger table (history table maintained automatically):
CREATE TABLE dbo.Hesap (
    HesapId int           NOT NULL PRIMARY KEY,
    Bakiye  decimal(18,2) NOT NULL
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Hesap_History), LEDGER = ON);

-- Append-only ledger (UPDATE/DELETE are blocked):
CREATE TABLE dbo.IslemLog (Id bigint IDENTITY, Mesaj nvarchar(400))
WITH (LEDGER = ON (APPEND_ONLY = ON));

-- Who changed what, and when:
SELECT t.commit_time, t.principal_name, l.HesapId, l.Bakiye, l.ledger_operation_type_desc
FROM   dbo.Hesap_Ledger l
  JOIN sys.database_ledger_transactions t ON t.transaction_id = l.ledger_transaction_id
ORDER BY t.commit_time DESC;

-- Take a digest (store it somewhere trusted outside) and verify:
EXEC sys.sp_generate_database_ledger_digest;
EXEC sys.sp_verify_database_ledger
     N'{"database_name":"Musteri","block_id":0,"hash":"0x...","last_transaction_commit_time":"...","digest_time":"..."}';

What it does

Creates a ledger table that records row changes in a cryptographic hash chain; the _Ledger view shows who made each INSERT/UPDATE/DELETE and when, and verification against a stored digest proves the data was not altered afterwards (including by DBAs).

When to use it

For data whose immutability must be provable: financial records, audit trails, contract/approval records.

Caution

Ledger cannot be turned off after the table is created; a ledger table cannot be converted to a regular table, and a dropped ledger table is kept as a dropped ledger table. Updatable ledger writes a history row on every change and storage grows. Unless digests are stored outside the database in immutable storage (WORM, Azure immutable blob), verification has no evidential value. sp_verify_database_ledger fails with error 37498 without snapshot isolation.

Open in DBAtlas →

Related