Enable Soft Delete in Azure SQL/Server

SoftDelete.sql

------------------------------------------------------------
-- Enable Soft Delete in Azure SQL using Row-Level Security
-- Fully idempotent, correct dependency ordering
------------------------------------------------------------

------------------------------------------------------------
-- Step 1: Create database, login, and user
------------------------------------------------------------
USE master;
GO

IF NOT EXISTS (SELECT 1 FROM sys.sql_logins WHERE name = 'TodoDbUser')
BEGIN
    CREATE LOGIN TodoDbUser WITH PASSWORD = 'Long@12345';
END
GO

IF DB_ID('TodoDB') IS NULL
BEGIN
    CREATE DATABASE TodoDB;
END
GO

USE TodoDB;
GO

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'TodoDbUser')
BEGIN
    CREATE USER TodoDbUser FOR LOGIN TodoDbUser;
END
GO

------------------------------------------------------------
-- Step 2: Create table and seed data
------------------------------------------------------------
IF OBJECT_ID('dbo.Todos', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.Todos
    (
        Id        INT IDENTITY(1,1) PRIMARY KEY,
        Title     NVARCHAR(200) NOT NULL,
        State     NVARCHAR(20)  NOT NULL DEFAULT 'pending',
        IsDeleted BIT           NOT NULL DEFAULT 0
    );
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.Todos)
BEGIN
    INSERT INTO dbo.Todos (Title, State) VALUES
        ('Buy groceries',   'pending'),
        ('Walk the dog',    'completed'),
        ('Finish report',   'in-progress'),
        ('Call mom',        'pending'),
        ('Clean the house', 'completed');
END
GO

------------------------------------------------------------
-- Step 3: Drop RLS policy before touching the function
------------------------------------------------------------
IF EXISTS (SELECT 1 FROM sys.security_policies WHERE name = 'TodosFilterPolicy')
BEGIN
    DROP SECURITY POLICY dbo.TodosFilterPolicy;
END
GO

------------------------------------------------------------
-- Step 4: Define soft delete behavior
------------------------------------------------------------
CREATE OR ALTER PROCEDURE dbo.DeleteTodo
    @Id INT
AS
    UPDATE dbo.Todos
    SET IsDeleted = 1
    WHERE Id = @Id;
GO

------------------------------------------------------------
-- Step 5: Grant application permissions
------------------------------------------------------------
GRANT SELECT, INSERT, UPDATE ON dbo.Todos TO TodoDbUser;
GRANT EXECUTE ON dbo.DeleteTodo TO TodoDbUser;
GO

------------------------------------------------------------
-- Step 6: Row-Level Security predicate function
------------------------------------------------------------
CREATE OR ALTER FUNCTION dbo.fn_SoftDeletePredicate(@IsDeleted BIT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS fn_result
    WHERE
        (
            DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('TodoDbUser')
            AND @IsDeleted = 0
        )
        OR DATABASE_PRINCIPAL_ID() <> DATABASE_PRINCIPAL_ID('TodoDbUser');
GO

------------------------------------------------------------
-- Step 7: Recreate Row-Level Security policy
------------------------------------------------------------
CREATE SECURITY POLICY dbo.TodosFilterPolicy
ADD FILTER PREDICATE dbo.fn_SoftDeletePredicate(IsDeleted)
    ON dbo.Todos,
ADD BLOCK PREDICATE dbo.fn_SoftDeletePredicate(IsDeleted)
    ON dbo.Todos AFTER UPDATE
WITH (STATE = ON);
GO

------------------------------------------------------------
-- Step 8: Demonstrate behavior
------------------------------------------------------------

-- Admin sees all rows
SELECT * FROM dbo.Todos;
GO

-- Soft delete as application user
EXECUTE AS USER = 'TodoDbUser';
EXEC dbo.DeleteTodo @Id = 2;
REVERT;
GO

-- Application user sees only active rows
EXECUTE AS USER = 'TodoDbUser';
SELECT * FROM dbo.Todos;
REVERT;
GO

-- Attempt undelete as application user
EXECUTE AS USER = 'TodoDbUser';
UPDATE dbo.Todos
SET IsDeleted = 0
WHERE Id = 2;
REVERT;
GO

-- Admin still sees all rows
SELECT * FROM dbo.Todos;
GO