0

When I attempt to drop an external table in Azure Synapse SQL Pool I get the folloiwng error:

Cannot drop the EXTERNAL TABLE 'TableName', because it does not exist or you do not have permission.

I am accessing Synapse SQL Server via SSMS.

Can someone let me know how elevate my permissions to drop an external table please.

Patterson
  • 1,927
  • 1
  • 19
  • 56
  • What permissions do you currently have? and can you administer the DB? or is it someone else? You'll need ALTER permission on the schema to which the table belongs or ALTER ANY EXTERNAL DATA SOURCE or ALTER ANY EXTERNAL FILE FORMAT to do this – Jon Dec 18 '22 at 19:13

1 Answers1

0

Theis error generally cause two reasons one is you are table might not present in that particular data baser or the user with which you are querying that table has not have proper privileges.

To Drop Any external table, you need below three major permissions like Alter any schema, alter any external data source and alter any external file format as @Jon suggested.

GRANT ALTER ANY SCHEMA to {user};

GRANT ALTER ANY EXTERNAL DATA SOURCE to {user};

GRANT ALTER ANY EXTERNAL FILE FORMAT to {user};

And also db_exporter server role is there which grant all this permission to you user.

ALTER SERVER ROLE db_exporter ADD MEMBER {user} ;

Also make sure you are using appropriate database to be querying your table where it is present.

enter image description here

And also make sure there is no DENY permission on your user

Pratik Lad
  • 4,343
  • 2
  • 3
  • 11