Azure SQL - can't drop system versioned tables

Question

Azure SQL - can't drop system versioned tables

Vitalii Bohachov 0

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?

2 answers

Your answer

Answer 1

Olaf Helper 46,201

ALTER TABLE dbo.Eployees

Typo? Eployees vs Employees

Vitalii Bohachov 0 Reputation points

2025-02-26T13:51:23.01+00:00

Yes. ALTER TABLE dbo.Employees

Answer 2

Erland Sommarskog 119.3K MVP

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

Vitalii Bohachov 0 Reputation points

2025-02-27T07:58:46.55+00:00

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

I try from administrator user, which was created when the instance was deployed.

Also I try from EntraID Administrator and this doesn't work either
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Erland Sommarskog 119.3K Reputation points MVP

2025-02-27T21:59:11.06+00:00
With any of these users that you think are admin, what does this query return:

SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE')

For an admin it should return a little more than 100 rows, and on of the rows should read CONTROL.
Vitalii Bohachov 0 Reputation points

2025-03-07T14:42:03.5933333+00:00

This query returns 0 rows.

But I dont have sa login for Azure SQL instance.
I connect as a user who is specified as the server admin
Erland Sommarskog 119.3K Reputation points MVP

2025-03-07T19:59:40.62+00:00

Zero rows that sounds spooky. You should have at least one row, which says that you have CONNECT permission. Else you would not be able to get into the database. Either you made some typo when you ran the command - or something is really wacko.

What if you try any other DDL, like creating a table?
Mahesh Kurva 2,920 Reputation points Microsoft External Staff

2025-03-10T17:49:46.2066667+00:00

Hi Vitalii Bohachov,

We haven’t heard back from you regarding your last response, @Erland Sommarskog, and I just wanted to check in to see if you’ve found a resolution yet. If so, please share your solution with the community, as it could be helpful to others. If the issue is still unresolved, feel free to provide more details, and we’ll do our best to assist you.
Vitalii Bohachov 0 Reputation points

2025-03-11T10:30:10.4633333+00:00

What if you try any other DDL, like creating a table?

Yes. Create table and Drop table operations works well.

SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE')

returns without CONTROL permission
Mahesh Kurva 2,920 Reputation points Microsoft External Staff

2025-03-11T18:04:38.0166667+00:00

Hi Vitalii Bohachov,

Is your issue resolved?
Erland Sommarskog 119.3K Reputation points MVP

2025-03-11T22:16:41.8233333+00:00
Is your issue resolved?

Manesh, what on Earth makes you think there is any point in asking this question? Does Vitalii's post in any way suggest this?

Vitalii, there are some hefty permissions there, but not enough to verify that you should be able to alter the table.

If you run:

SELECT * FROM sys.fn_my_permissions('dbo.YourTemporalTable', 'OBJECT')

What permissions do you have?

Share via

Azure SQL - can't drop system versioned tables

2 answers

Your answer