Skip to content

block-crud-tsql.md

Latest commit

 

History

History
113 lines (82 loc) · 5.74 KB

block-crud-tsql.md

File metadata and controls

113 lines (82 loc) · 5.74 KB
title Block T-SQL Commands To Create Or Modify Azure SQL Resources
description This article details features allowing Azure administrators to block T-SQL commands to create or modify Azure SQL Database and Azure SQL Managed Instance resources.
author WilliamDAssafMSFT
ms.author wiassaf
ms.reviewer wiassaf, mathoma
ms.date 03/10/2026
ms.service azure-sql
ms.subservice security
ms.topic how-to
ROBOTS NOINDEX
monikerRange =azuresql || =azuresql-db
ms.custom sfi-image-nochange

What is Block T-SQL CRUD?

[!INCLUDEappliesto-sqldb-sqlmi]

The Block T-SQL CRUD features allow Azure administrators to block the creation or modification of Azure SQL resources through T-SQL. Two separate subscription-level preview feature flags are available:

Preview feature flag Scope
Block T-SQL CRUD for logical servers (block-tsql-crud) Azure SQL Database (logical server)
Block T-SQL CRUD for managed instances (block-tsql-mi-crud) Azure SQL Managed Instance

Each flag is registered independently per subscription. You can enable one or both depending on which Azure SQL services you need to govern.

Overview

To block creation or modification of resources through T-SQL and enforce resource management through an Azure Resource Manager template (ARM template) for a given subscription, the subscription-level preview features in the Azure portal can be used. This is particularly useful when you are using Azure Policies to enforce organizational standards through ARM templates. Since T-SQL does not adhere to Azure Policies, a block on T-SQL create or modify operations can be applied.

T-SQL CRUD operations can be blocked via the Azure portal, PowerShell, or Azure CLI.

Blocked statements for Azure SQL Database

When the Block T-SQL CRUD for logical servers (block-tsql-crud) preview feature is registered, the following T-SQL statements are blocked for Azure SQL Database resources:

  1. CREATE DATABASE
  2. DROP DATABASE
  3. CREATE DATABASE ... AS COPY OF
  4. ALTER DATABASE (edition, service objective, max size, etc.)
  5. ALTER DATABASE ... ADD SECONDARY ON SERVER
  6. ALTER DATABASE ... REMOVE SECONDARY ON SERVER
  7. ALTER DATABASE ... FAILOVER

Blocked statements for Azure SQL Managed Instance

When the Block T-SQL CRUD for managed instances (block-tsql-mi-crud) preview feature is registered, the following T-SQL statements are blocked for Azure SQL Managed Instance resources:

  1. CREATE DATABASE
  2. DROP DATABASE
  3. Cancel in-progress CREATE DATABASE
  4. RESTORE DATABASE ... FROM URL
  5. ALTER DATABASE ... ADD FILE
  6. ALTER DATABASE ... MODIFY FILE
  7. ALTER DATABASE ... REMOVE FILE (on geo-replicated file)
  8. ALTER DATABASE tempdb ADD FILE
  9. ALTER DATABASE tempdb MODIFY FILE
  10. ALTER DATABASE tempdb REMOVE FILE
  11. ALTER DATABASE ... SET (compatibility level, collation, etc.)
  12. ALTER DATABASE ... SET ENCRYPTION ON/OFF
  13. ALTER AVAILABILITY GROUP ... FAILOVER (MI Link / Failover Group)
  14. Failover stored procedure configuration
  15. DBCC TRACEON / DBCC TRACEOFF (global trace flags)
  16. sp_configure (SQL Agent enable/disable)
  17. sp_configure / MSDTC transition to primary
  18. MSDTC network settings (XA, LU, inbound/outbound)
  19. Vulnerability Assessment scan trigger via T-SQL

Permissions

In order to register or remove either feature, the Azure user must be a member of the Owner or Contributor role of the subscription.

Examples

The following section describes how you can register or unregister a preview feature with the Microsoft.Sql resource provider in the Azure portal.

Register a Block T-SQL CRUD feature

  1. Go to your subscription in the Azure portal.
  2. Select the Preview Features tab.
  3. Select the feature flag you want to enable:
    • Block T-SQL CRUD for logical servers — for Azure SQL Database
    • Block T-SQL CRUD for managed instances — for Azure SQL Managed Instance
  4. In the window that opens, select Register to register this block with the Microsoft.Sql resource provider.

:::image type="content" source="media/block-crud-tsql/block-tsql-crud-register.png" alt-text="With 'Block T-SQL CRUD' checked, select Register." lightbox="media/block-crud-tsql/block-tsql-crud-register.png":::

Re-register Microsoft.Sql resource provider

After you register either block feature with the Microsoft.Sql resource provider, you must re-register the Microsoft.Sql resource provider for the changes to take effect. To re-register the Microsoft.Sql resource provider:

  1. Go to your subscription in the Azure portal.
  2. Select the Resource Providers tab.
  3. Search and select Microsoft.Sql resource provider.
  4. Select Re-register.

Note

The re-registration step is mandatory for the T-SQL block to be applied to your subscription.

:::image type="content" source="media/block-crud-tsql/block-tsql-crud-re-register.png" alt-text="Screenshot of the Azure portal showing how to re-register the Microsoft.Sql resource provider." lightbox="media/block-crud-tsql/block-tsql-crud-re-register.png":::

Remove Block T-SQL CRUD

To remove the block on T-SQL create or modify operations from your subscription, first unregister the previously registered T-SQL block. Then, re-register the Microsoft.Sql resource provider as shown above for the removal of T-SQL block to take effect.

Related content