Sometimes applications need to remove data without actually losing it. Soft delete keeps rows in the database while making them invisible to normal application access. This is especially valuable when exposing a database to an AI agent through MCP servers like SQL MCP Server, where safety and reversibility matter.

Learn more about SQL MCP Server

Filtering on an IsDeleted column in every query is fragile. One missed filter exposes your data. Row Level Security enforces visibility rules inside the database so application code cannot bypass them.

Working demo https://gist.github.com

What We Are Building

A Todos table where soft delete is enforced at the database layer.

Step 1: Create the User and Login

This example uses a SQL login for simplicity. In Azure SQL, you would typically use a managed identity. The pattern is the same.

CREATE LOGIN TodoDbUser WITH PASSWORD = 'Long@12345';
GO

CREATE USER TodoDbUser FOR LOGIN TodoDbUser;
GO

Step 2: Create the Table and Sample Data

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
);
GO

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');
GO

Step 3: Define Soft Delete

Instead of issuing a DELETE, the application calls a stored procedure that marks the row as deleted.

CREATE PROCEDURE dbo.DeleteTodo
    @Id INT
AS
    UPDATE dbo.Todos
    SET IsDeleted = 1
    WHERE Id = @Id;
GO

This stored procedure is the only delete mechanism exposed to the application.

Step 4: Grant Permissions

The application can select and update rows, but it cannot delete them directly.

GRANT SELECT, INSERT, UPDATE ON dbo.Todos TO TodoDbUser;
GRANT EXECUTE ON dbo.DeleteTodo TO TodoDbUser;
GO

At this point, the application still sees deleted rows. That changes next.

Step 5: Enforce Visibility with Row Level Security

Row Level Security controls which rows are visible to which users. It applies to SELECT, UPDATE, and DELETE. Filtering happens before the statement executes.

The Predicate Function

CREATE 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

Meaning:

• TodoDbUser sees only rows where IsDeleted = 0
• All other users see all rows

Bind the Predicate

CREATE SECURITY POLICY dbo.TodosFilterPolicy
ADD FILTER PREDICATE dbo.fn_SoftDeletePredicate(IsDeleted)
ON dbo.Todos
WITH (STATE = ON);
GO

From this point forward, deleted rows are invisible to the application user.

Seeing It Work

Admin View

SELECT * FROM dbo.Todos;

Result

Id	Title	State	IsDeleted
1	Buy groceries	pending	0
2	Walk the dog	completed	0
3	Finish report	in-progress	0
4	Call mom	pending	0
5	Clean the house	completed	0

Soft Delete a Row as User

EXECUTE AS USER = 'TodoDbUser';
EXEC dbo.DeleteTodo @Id = 2;
REVERT;

User View

EXECUTE AS USER = 'TodoDbUser';
SELECT * FROM dbo.Todos;
REVERT;

Result

Id	Title	State	IsDeleted
1	Buy groceries	pending	0
3	Finish report	in-progress	0
4	Call mom	pending	0
5	Clean the house	completed	0

The deleted row is hidden from the application user but remains visible to admins.

Admin View After Delete

SELECT * FROM dbo.Todos;

Result

Id	Title	State	IsDeleted
1	Buy groceries	pending	0
2	Walk the dog	completed	1
3	Finish report	in-progress	0
4	Call mom	pending	0
5	Clean the house	completed	0

Why Undelete Does Not Work

EXECUTE AS USER = 'TodoDbUser';

UPDATE dbo.Todos
SET IsDeleted = 0
WHERE Id = 2;

REVERT;

The application user cannot undelete a row. Row Level Security hides deleted rows before the UPDATE executes, so the statement matches zero rows.

Optional: Explicitly Block Undelete Attempts

If you prefer explicit enforcement rather than silent filtering, add a block predicate.

ALTER SECURITY POLICY dbo.TodosFilterPolicy
ADD BLOCK PREDICATE dbo.fn_SoftDeletePredicate(IsDeleted)
ON dbo.Todos AFTER UPDATE;
GO

Using Managed Identity in Azure SQL

To use a managed identity instead of a SQL login:

CREATE USER [my-container-app] FROM EXTERNAL PROVIDER;

Reference that user in the predicate function. Everything else stays the same.