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 an MCP server like SQL MCP Server, where safety and reversibility matter.

Learn 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. Let’s take a look.

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 & 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

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. Let’s fix that.

Step 5: Enforce Visibility

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;

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;

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

Why Undelete Does Not Work

EXECUTE AS USER = 'TodoDbUser';

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

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;

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.