{"id":6384,"date":"2026-02-05T16:47:59","date_gmt":"2026-02-06T00:47:59","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=6384"},"modified":"2026-02-23T11:37:01","modified_gmt":"2026-02-23T19:37:01","slug":"soft-delete-in-azure-sql","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/soft-delete-in-azure-sql\/","title":{"rendered":"Enable Soft Delete in Azure SQL"},"content":{"rendered":"

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.<\/p>\n

\n

Learn more about SQL MCP Server<\/a><\/p>\n<\/blockquote>\n

Filtering on an IsDeleted<\/code> 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.<\/p>\n

\n

Working demo https:\/\/gist.github.com<\/a><\/p>\n<\/blockquote>\n

What We Are Building<\/h2>\n

A Todos<\/code> table where soft delete is enforced at the database layer.<\/p>\n

Step 1: Create the User and Login<\/h2>\n

This example uses a SQL login for simplicity. In Azure SQL, you would typically use a managed identity. The pattern is the same.<\/p>\n

CREATE LOGIN TodoDbUser WITH PASSWORD = 'Long@12345';\nGO\n\nCREATE USER TodoDbUser FOR LOGIN TodoDbUser;\nGO\n<\/code><\/pre>\n
Step 2: Create the Table and Sample Data<\/h2>\n
CREATE TABLE dbo.Todos\n(\n    Id        INT IDENTITY(1,1) PRIMARY KEY,\n    Title     NVARCHAR(200) NOT NULL,\n    State     NVARCHAR(20)  NOT NULL DEFAULT 'pending',\n    IsDeleted BIT           NOT NULL DEFAULT 0\n);\nGO\n\nINSERT INTO dbo.Todos (Title, State) VALUES\n    ('Buy groceries',   'pending'),\n    ('Walk the dog',    'completed'),\n    ('Finish report',   'in-progress'),\n    ('Call mom',        'pending'),\n    ('Clean the house', 'completed');\nGO\n<\/code><\/pre>\n
Step 3: Define Soft Delete<\/h2>\n
Instead of issuing a DELETE<\/code>, the application calls a stored procedure that marks the row as deleted.<\/p>\n
CREATE PROCEDURE dbo.DeleteTodo\n    @Id INT\nAS\n    UPDATE dbo.Todos\n    SET IsDeleted = 1\n    WHERE Id = @Id;\nGO\n<\/code><\/pre>\n
This stored procedure is the only delete mechanism exposed to the application.<\/p>\n
Step 4: Grant Permissions<\/h2>\n
The application can select and update rows, but it cannot delete them directly.<\/p>\n
GRANT SELECT, INSERT, UPDATE ON dbo.Todos TO TodoDbUser;\nGRANT EXECUTE ON dbo.DeleteTodo TO TodoDbUser;\nGO\n<\/code><\/pre>\n
At this point, the application still sees deleted rows. That changes next.<\/p>\n
Step 5: Enforce Visibility with Row Level Security<\/h2>\n
Row Level Security controls which rows are visible to which users. It applies to SELECT<\/code>, UPDATE<\/code>, and DELETE<\/code>. Filtering happens before the statement executes.<\/p>\n
The Predicate Function<\/h3>\n
CREATE FUNCTION dbo.fn_SoftDeletePredicate(@IsDeleted BIT)\nRETURNS TABLE\nWITH SCHEMABINDING\nAS\nRETURN\n    SELECT 1 AS fn_result\n    WHERE\n        (\n            DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('TodoDbUser')\n            AND @IsDeleted = 0\n        )\n        OR DATABASE_PRINCIPAL_ID() <> DATABASE_PRINCIPAL_ID('TodoDbUser');\nGO\n<\/code><\/pre>\n
Meaning:<\/p>\n
    \n
  • TodoDbUser<\/code> sees only rows where IsDeleted = 0<\/code><\/li>\n
  • All other users see all rows<\/li>\n<\/ul>\n
    Bind the Predicate<\/h3>\n
    CREATE SECURITY POLICY dbo.TodosFilterPolicy\nADD FILTER PREDICATE dbo.fn_SoftDeletePredicate(IsDeleted)\nON dbo.Todos\nWITH (STATE = ON);\nGO\n<\/code><\/pre>\n
    From this point forward, deleted rows are invisible to the application user.<\/p>\n
    Seeing It Work<\/h2>\n
    Admin View<\/h3>\n
    SELECT * FROM dbo.Todos;\n<\/code><\/pre>\n
    Result<\/h4>\n\n\n\n\n\n\n\n\n\n
    Id<\/th>\nTitle<\/th>\nState<\/th>\nIsDeleted<\/th>\n<\/tr>\n<\/thead>\n
    1<\/td>\nBuy groceries<\/td>\npending<\/td>\n0<\/td>\n<\/tr>\n
    2<\/td>\nWalk the dog<\/td>\ncompleted<\/td>\n0<\/td>\n<\/tr>\n
    3<\/td>\nFinish report<\/td>\nin-progress<\/td>\n0<\/td>\n<\/tr>\n
    4<\/td>\nCall mom<\/td>\npending<\/td>\n0<\/td>\n<\/tr>\n
    5<\/td>\nClean the house<\/td>\ncompleted<\/td>\n0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Soft Delete a Row as User<\/h3>\n
    EXECUTE AS USER = 'TodoDbUser';\nEXEC dbo.DeleteTodo @Id = 2;\nREVERT;\n<\/code><\/pre>\n
    User View<\/h3>\n
    EXECUTE AS USER = 'TodoDbUser';\nSELECT * FROM dbo.Todos;\nREVERT;\n<\/code><\/pre>\n
    Result<\/h4>\n\n\n\n\n\n\n\n\n
    Id<\/th>\nTitle<\/th>\nState<\/th>\nIsDeleted<\/th>\n<\/tr>\n<\/thead>\n
    1<\/td>\nBuy groceries<\/td>\npending<\/td>\n0<\/td>\n<\/tr>\n
    3<\/td>\nFinish report<\/td>\nin-progress<\/td>\n0<\/td>\n<\/tr>\n
    4<\/td>\nCall mom<\/td>\npending<\/td>\n0<\/td>\n<\/tr>\n
    5<\/td>\nClean the house<\/td>\ncompleted<\/td>\n0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The deleted row is hidden from the application user but remains visible to admins.<\/p>\n
    Admin View After Delete<\/h3>\n
    SELECT * FROM dbo.Todos;\n<\/code><\/pre>\n
    Result<\/h4>\n\n\n\n\n\n\n\n\n\n
    Id<\/th>\nTitle<\/th>\nState<\/th>\nIsDeleted<\/th>\n<\/tr>\n<\/thead>\n
    1<\/td>\nBuy groceries<\/td>\npending<\/td>\n0<\/td>\n<\/tr>\n
    2<\/td>\nWalk the dog<\/td>\ncompleted<\/td>\n1<\/td>\n<\/tr>\n
    3<\/td>\nFinish report<\/td>\nin-progress<\/td>\n0<\/td>\n<\/tr>\n
    4<\/td>\nCall mom<\/td>\npending<\/td>\n0<\/td>\n<\/tr>\n
    5<\/td>\nClean the house<\/td>\ncompleted<\/td>\n0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Why Undelete Does Not Work<\/h2>\n
    EXECUTE AS USER = 'TodoDbUser';\n\nUPDATE dbo.Todos\nSET IsDeleted = 0\nWHERE Id = 2;\n\nREVERT;\n<\/code><\/pre>\n
    The application user cannot undelete a row. Row Level Security hides deleted rows before the UPDATE<\/code> executes, so the statement matches zero rows.<\/p>\n
    Optional: Explicitly Block Undelete Attempts<\/h2>\n
    If you prefer explicit enforcement rather than silent filtering, add a block predicate.<\/p>\n
    ALTER SECURITY POLICY dbo.TodosFilterPolicy\nADD BLOCK PREDICATE dbo.fn_SoftDeletePredicate(IsDeleted)\nON dbo.Todos AFTER UPDATE;\nGO\n<\/code><\/pre>\n
    Using Managed Identity in Azure SQL<\/h2>\n
    To use a managed identity instead of a SQL login:<\/p>\n
    CREATE USER [my-container-app] FROM EXTERNAL PROVIDER;\n<\/code><\/pre>\n
    Reference that user in the predicate function. Everything else stays the same.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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 […]<\/p>\n","protected":false},"author":96788,"featured_media":6603,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1,597,710,619],"tags":[560,38,711,34],"class_list":["post-6384","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","category-data-api-builder-2","category-sql-mcp-server","category-t-sql","tag-data-api-builder","tag-security","tag-sql-mcp-server","tag-t-sql"],"acf":[],"blog_post_summary":"
    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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/6384","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/96788"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=6384"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/6384\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/6603"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=6384"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=6384"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=6384"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}