CODEWITHSUNDEEP
azure-synapse
Pasquale
Pasquale

Reputation: 29

Differencies between Synapse Administrator and Synapse SQL Administator roles to access a serverless SQL pool

in the same tenant/subscription I can use two Synapse Analytics workspaces, asaws1 and asaws2. For both workspaces my Microsoft Entra user (with MFA) is a Synapse Administrator.

Using Synapse Studio or SSMS, I can access to the asaws1 serverless SQL pool successfully, but I cannot access to the asaws2 serverless SQL pool. If I add my user to the Synapse SQL Administrator role for asaws2 workspace I can access to the related serverless SQL pool without any problems.

The asaws1 workspace is older than the asaws2 one. Are there recent changes about the Synapse Administrator and Synapse SQL Administrator roles?

Is the Synapse SQL Administrator role a complementary role than the Synapse Administrator role?

I know that the Synapse Administrator role includes all Synapse SQL Administrator permissions. Any suggests to me in order to solve a such issue, please? Thanks

Upvotes: 0

Views: 110

Answers (1)

Jon
Jon

Reputation: 4967

There is a difference between the role of Synapse Administrator and Synapse SQL Administrator.

Synapse Administrator manages the Synapse instance, for items like Synapse workspaces, monitoring, security, and other administration tasks.

Synapse SQL Administrator deals with the dedicated and serverless pools and manages those SQL resources within the Synapse workspace, not the whole workspace items, like linked services, pipelines etc.

You can run queries as a Synapse SQL Administrator for both DML (SQL querys) and DDL (SQL Drop/Create etc)

It maybe that you had a User login on the asaws1 but not on asaws2, giving you the elevated permissions of Synapse SQL Administrator on asaws2 allowed you to access the database. I would check your permissions and role assignment in both work spaces and check what users have been added to which SQL instance.

Upvotes: 0

Related Questions