Add Your AAD Group to Dedicated or Serverless SQL Pool

It can be confusing that we can create Synapse, see that it comes with Serverless SQL Pool, setup a Dedicated SQL Pool, and then not be able to access it. SQL Server Databases – even serverless ones – still need logins, users, and roles to be able to work.

Here is a quick rundown to get you on your way.

When we add users to the database, best practices would generally tell you to use a Security Group.

Use database_principals to check for your login to already be in the database. Principals include user database users, database roles, and application roles. If you’re not in this list, then you don’t have access!

SELECT * FROM SYS.DATABASE_PRINCIPALS 

For our AAD principals, we are always going to use FROM EXTERNAL PROVIDER.

Create a Login for your AAD Security Principal

First things first: we need to create a login for the database. We create a login so that way the security principal can authenticate. Logins are created at the server level, so we can do this from the master database.

CREATE LOGIN MyAzureSecurityGroup FROM EXTERNAL PROVIDER;

Create a User for your AAD Security Principal

There are many options for creating a user in a SQL Server. In fact, at present there are thirteen different user types that you can create. We create a user so that way the security principal can authorize for its role. Users are created at the database level, so change to the database you’re setting up.

USE [MyDb]
CREATE USER MyAzureSecurityGroup FROM EXTERNAL PROVIDER;

Add a Role to Your User

Database Roles include db_owner, db_datareader, db_datawriter among others. We need to add the user to a role to grant the permissions needed to operate in the database.

EXEC sp_addrolemember 'db_datawriter','MyAzureSecurityGroup';

Or…….

ALTER ROLE db_datawriter ADD MEMBER MyAzureSecurityGroup;

That’s it! You should be good to go now. Less talk. More examples.