How to block T-SQL CRUD

Applies to: Azure SQL Database Azure SQL Managed Instance

This article teaches you how to use the block T-SQL CRUD feature for Azure SQL resources. By using this feature, Azure administrators can block the creation or modification of Azure SQL resources through T-SQL.

You can block T-SQL CRUD operations at the subscription level for the following resources:

The logical server in Azure
Azure SQL Managed Instance

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, use the subscription-level preview features in the Azure portal. This approach is particularly useful when you're using Azure Policies to enforce organizational standards through ARM templates. Since T-SQL doesn't adhere to Azure Policies, you can block T-SQL create or modify operations.

You can block T-SQL CRUD operations through the Azure portal, PowerShell, or Azure CLI.

Blocked statements

Blocked statements differ between the logical server and SQL managed instance.

Logical server
SQL managed instance

When you register the Block T-SQL CRUD for logical servers (block-tsql-crud) feature, the feature blocks the following T-SQL statements for resources associated with the logical server:

CREATE DATABASE
DROP DATABASE
CREATE DATABASE ... AS COPY OF
ALTER DATABASE (edition, service objective, max size, and other settings)
ALTER DATABASE ... ADD SECONDARY ON SERVER
ALTER DATABASE ... REMOVE SECONDARY ON SERVER
ALTER DATABASE ... FAILOVER

When you register the Block T-SQL CRUD for managed instances (block-tsql-mi-crud) feature, the feature blocks the following T-SQL statements for Azure SQL Managed Instance resources:

CREATE DATABASE
DROP DATABASE
Cancel in-progress CREATE DATABASE
RESTORE DATABASE ... FROM URL
ALTER DATABASE ... ADD FILE
ALTER DATABASE ... MODIFY FILE
ALTER DATABASE ... REMOVE FILE (on geo-replicated file)
ALTER DATABASE tempdb ADD FILE
ALTER DATABASE tempdb MODIFY FILE
ALTER DATABASE tempdb REMOVE FILE
ALTER DATABASE ... SET (compatibility level, collation, and other settings)
ALTER DATABASE ... SET ENCRYPTION ON/OFF
ALTER AVAILABILITY GROUP ... FAILOVER (MI Link / Failover Group)
Failover stored procedure configuration
DBCC TRACEON / DBCC TRACEOFF (global trace flags)
sp_configure (SQL Agent enable/disable)
sp_configure / MSDTC transition to primary
MSDTC network settings (XA, LU, inbound/outbound)
Vulnerability Assessment scan trigger via T-SQL

Permissions

To register or remove either feature, you must be a member of the Owner or Contributor role for the subscription.

Enable blocking T-SQL CRUD features

You can enable the feature for the associated Azure SQL resource by using the Azure portal, PowerShell, or the Azure CLI.

The following table lists the name of the feature for the associated Azure SQL resource:

Feature name	Scope
Block T-SQL CRUD for logical servers (`block-tsql-crud`)	The logical server in Azure
Block T-SQL CRUD for SQL managed instances (`block-tsql-mi-crud`)	Azure SQL Managed Instance

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

Note

Although you can enable and disable T-SQL CRUD blocking by using the Preview feature functionality in the Azure portal, the block T-SQL CRUD feature is generally available for both Azure SQL Database and Azure SQL Managed Instance.

To enable the feature for your subscription in the Azure portal, follow these steps:

Go to your subscription in the Azure portal.
Under Settings, select Preview Features to open the Preview features pane.
On the Preview features pane,
1. Enter CRUD in the search box.
2. Select the checkbox for the features you want to register for the associated resource. The two features related to blocking T-SQL CRUD operations for Azure SQL resources are:
  - Block T-SQL CRUD for logical servers — for Azure SQL Database
  - Block T-SQL CRUD for managed instances — for Azure SQL Managed Instance
3. Select Register on the command bar to register the feature to your subscription.

Use Register-AzProviderFeature to register the feature for your subscription.

The following example registers the block T-SQL CRUD feature for logical servers:

Register-AzProviderFeature -FeatureName "block-tsql-crud" -ProviderNamespace "Microsoft.Sql"

The following example registers the block T-SQL CRUD feature for SQL managed instances:

Register-AzProviderFeature -FeatureName "block-tsql-mi-crud" -ProviderNamespace "Microsoft.Sql"

To check the registration status, use Get-AzProviderFeature:

Get-AzProviderFeature -FeatureName "block-tsql-crud" -ProviderNamespace "Microsoft.Sql"

Use az feature register to register the feature for your subscription.

The following example registers the block T-SQL CRUD feature for logical servers:

az feature register --name block-tsql-crud --namespace Microsoft.Sql

The following example registers the block T-SQL CRUD feature for SQL managed instances:

az feature register --name block-tsql-mi-crud --namespace Microsoft.Sql

To check the registration status, use az feature show:

az feature show --name block-tsql-crud --namespace Microsoft.Sql --output table

Re-register the 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.

Note

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

To re-register the Microsoft.Sql resource provider in the Azure portal, follow these steps:

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

Screenshot of the Azure portal showing how to re-register the Microsoft.Sql resource provider.

Use Register-AzResourceProvider to re-register the Microsoft.Sql resource provider:

Register-AzResourceProvider -ProviderNamespace "Microsoft.Sql"

Use az provider register to re-register the Microsoft.Sql resource provider:

az provider register --namespace Microsoft.Sql

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 feature. Then, re-register the Microsoft.Sql resource provider for the removal to take effect.

To unregister the feature in the Azure portal:

Go to your subscription in the Azure portal.
Under Settings, select Preview Features.
Select the feature you want to unregister.
Select Unregister.

Use Unregister-AzProviderFeature to unregister the feature:

Unregister-AzProviderFeature -FeatureName "block-tsql-crud" -ProviderNamespace "Microsoft.Sql"

For SQL managed instances:

Unregister-AzProviderFeature -FeatureName "block-tsql-mi-crud" -ProviderNamespace "Microsoft.Sql"

After unregistering, re-register the resource provider:

Register-AzResourceProvider -ProviderNamespace "Microsoft.Sql"

Use az feature unregister to unregister the feature.

The following example unregisters the feature for logical servers:

az feature unregister --name block-tsql-crud --namespace Microsoft.Sql

The following example unregisters the feature for SQL managed instances:

az feature unregister --name block-tsql-mi-crud --namespace Microsoft.Sql

After unregistering the feature, use the following command to re-register the resource provider:

az provider register --namespace Microsoft.Sql

Feedback

Was this page helpful?

Last updated on 2026-03-31