Search code examples
sql-serverpermissionsazure-sql-databasesql-grant

Is it possible to use this SWITCH PARTITION control option with Azure SQL Server?

I'm doing some ETL, using the standard "Pre-Load" partition pattern: Load the data into a dated partition of a loading table, then SWITCH that partition into the live table.

I found these options for the SWITCH command:

ALTER TABLE [myLoadingTable] SWITCH PARTITION @partNum TO [myLiveTable] PARTITION @partNum -- Move the New Data in.
    WITH ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ))

Those options seem like valuable things to define (even if they wouldn't be needed during normal operation).

Unfortunately, when I try to run that, I get:

Sql error number: 11423.

Error Message: User does not have permission to use the ABORT_AFTER_WAIT = BLOCKERS option.

Further reading confirms that this is appropriate: (link)

BLOCKERS Kill all user transactions that currently block the SWITCH or online index rebuild DDL operation so that the operation can continue.

Requires ALTER ANY CONNECTION permission.

But when I try to GRANT the relevant user that permission I get an error:

GRANT ALTER ANY CONNECTION TO [myAdfUser]

Securable class 'server' not supported in this version of SQL Server.

I'm using (AFAIK) a normal Azure SQL Server database.

Is there any work around for this? Or is it just not possible for me to use these options on this Database?

Solution

  • Looks the question was solved by @Larnu's comment, just add it as an answer to close the question.

    If you are using Azure SQL Database, then what the error is telling you is true. Azure SQL Databases are what are known as Partially Contained databases; things like their USER objects have their own Password and the LOGIN objects on the server aren't used for connections. The CONNECTION permission is a server level permission, and thus not supported in Azure SQL Databases.