Blog Post

Azure Database Support Blog
2 MIN READ

Error “TSQL CRUD has been disallowed via policy for this Azure subscription“

mohammad_belbaisi's avatar
Jun 25, 2023

We received some support cases when customers encounter following error “TSQL CRUD has been disallowed via policy for this Azure subscription“ while trying to create or modify of Azure SQL resources through T-SQL.

 

Cause

 

This error occurred when Azure Administrator block the T-SQL CRUD operations of Azure SQL resources through T-SQL. This is enforced at the subscription level to block T-SQL commands from affecting SQL resources in any Azure SQL database.

 

How to enable block T-SQL CRUD

 

To use this feature “block T-SQL CRUD” operation an Azure user with owner or contributor role is needed, by following the below steps:

 

  1. Go to your subscription on Azure portal.
  2. Select the Preview Features tab.
  3. Select Block T-SQL CRUD.
  4. After you select Block T-SQL CRUD, a new window will open, select Register, to register this block with 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:

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

 

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.

 

When to use Block T-SQL CRUD feature

 

When you are using Azure Policies to enforce organizational standards through ARM templates. Since T-SQL does not adhere to the Azure Policies, a block on T-SQL create or modify operations can be applied. The syntax blocked includes CRUD (create, update, delete) statements for databases in Azure SQL, specifically CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements.

 

Blocked statement

 

  1. CREATE DATABASE statements
  2. DROP DATABASE statements
  3. 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 ...

 

 

Disclaimer


Please note that the products and options presented in this article are subject to change. This article reflects Block T-SQL CRUD in June 2023. To check for new update or more information please check the below link:

 

Block T-SQL commands to create or modify Azure SQL resources - Block T-SQL commands to create or modify Azure SQL resources | Microsoft Learn

 

I hope this article was helpful to you, please feel free to share your feedback in the comments section. 

 

Updated Jun 25, 2023
Version 1.0
No CommentsBe the first to comment