What is Block T-SQL CRUD feature?
Applies to: Azure SQL Database
This feature allows Azure administrators to block the creation or modification of Azure SQL Database resources through T-SQL. This is enforced at the subscription level to block T-SQL commands from affecting Azure SQL Database resources.
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 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. The syntax blocked includes CRUD (create, update, delete) operations for databases in Azure SQL Database.
T-SQL CRUD operations can be blocked via Azure portal, PowerShell, or Azure CLI.
Blocked statements
The following T-SQL statements are blocked when this feature is enabled:
CREATE DATABASE
statementsDROP DATABASE
statements- A subset of
ALTER DATABASE
statements, as follows:ALTER DATABASE ... ADD SECONDARY ON SERVER
ALTER DATABASE ... REMOVE SECONDARY ON SERVER
ALTER DATABASE ... FAILOVER
ALTER DATABASE ... MODIFY NAME ...
ALTER DATABASE ... MODIFY (MAXSIZE | EDITION | SERVICE_OBJECTIVE ...)
ALTER DATABASE ... MODIFY BACKUP_STORAGE_REDUNDANCY ...
ALTER DATABASE ... SET ENCRYPTION ...
Permissions
In order to register or remove this 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 Microsoft.Sql resource provider in Azure portal:
Register Block T-SQL CRUD
- Go to your subscription on Azure portal.
- Select the Preview Features tab.
- Select Block T-SQL CRUD.
- After you select Block T-SQL CRUD, a new window will open, select Register, to register this block with Microsoft.Sql resource provider.
Re-register Microsoft.Sql resource provider
After you register the block of T-SQL CRUD with 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:
- Go to your subscription on Azure portal.
- Select the Resource Providers tab.
- Search and select Microsoft.Sql resource provider.
- Select Re-register.
Note
The re-registration step is mandatory for the T-SQL block to be applied to your subscription.
Removing 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.