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

by | Jun 25, 2023 | Technology | 0 comments

This article is contributed. See the original author and article here.

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.


 


mohammad_belbaisi_4-1687089863775.png


 


mohammad_belbaisi_5-1687089883626.png


 


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.


mohammad_belbaisi_2-1687089629512.png


 


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. 


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment