Azure SQL - can't drop system versioned tables

Question

Hello.

I'm can't drop system versioned tables in master db in Azure SQL

I have a docs - https://learn.microsoft.com/en-us/sql/relational-databases/tables/stopping-system-versioning-on-a-system-versioned-temporal-table?view=azuresqldb-current

but request

ALTER TABLE dbo.Employees
SET (SYSTEM_VERSIONING = OFF);

returns an error: Msg 13538, Level 16, State 3, Line 1

You do not have the required permissions to complete the operation.

How I can delete this tables?

Answer 1

Vitalii Bohachov Thank you for being patient while working on this issue.

We investigated and confirmed that this is a bug. We will be making repairs to avoid this problem in the future. Currently, a system-versioned table created in the master database on a logical SQL server cannot be dropped.  Thank you for pointing this out.

We updated documentation to clarify that creating tables in the master database on a logical SQL server isn't supported. https://learn.microsoft.com/en-us/azure/azure-sql/database/transact-sql-tsql-differences-sql-server?view=azuresql#t-sql-syntax-statements-with-partial-differences

We can drop it at the back end for you.

Regards,

Oury

Answer 2

I've done some more research, and it looks like you are stuck with the beard in the letterbox as we say in Swedish.

I assume that your scenario is that you mistakenly created this temporal table in master and you want to drop it. But you can't drop a temporal table directly, you first need to disable system versioning. Unfortunately, this requires CONTROL permission, as discussed at https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-security?view=sql-server-ver16#security-principles. And as we have concluded you only have ALTER and VIEW DEFINITION on the table.

As for why you only have these permissions despite you are server admin, this seems to be by design. I see the same thing on my Azure SQL Servers. For a normal table, it is not a problem, since to drop a table you only need ALTER permission.

I guess you will have to live with this table. If you absolutely want to get rid of it, you would have to open a support case, so that the support staff can delete the table from the backend.

Note that if you only have a developer-level support contract, the way you do that is by open a question here in Microsoft Q&A. But you should do this through the portal, so that the question gets labelled as a support issue. As I understand it, the support person who gets assigned to the case will then contact through private message to get the details needed to connect to your server.

Answer 3

You need to find some who has sufficient permission. For this operation you need at ALTER permission on the table.

Answer 4

ALTER TABLE dbo.Eployees

Typo? Eployees vs Employees