Add user (AAD) to the Azure SQL Database
It’s a simple task: ‘Add the user to the database’. But when you’re used to on-premise environments, you have to think a little bit differently. It’s still simple, but different.
The big difference is the concept of contained databases where you don’t map users to a (server)login. In on-premise environments, a login gets access to the server and a user gets access to one or more databases. With Azure SQL Database a user can get both. This makes these databases more portable.
Add AAD user
What not to do
- Configure Access Control on the SQL-server resource in your Azure Portal. You might think you need to add a role assignment. But here you configure access to the SQL-server resource, not the database itself.
What to do
- Connect to the database with SSMS (using a user with sufficient permissions).
- Copy the code below and change the username and roles to your needs.
- Execute the code.
--Add User CREATE USER [otheruser@peekdata.nl] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo; --Make user database reader ALTER ROLE db_datareader ADD MEMBER [otheruser@peekdata.nl] ; --Or make user database admin ALTER ROLE db_owner ADD MEMBER [otheruser@peekdata.nl];
After creation, the user will be visible in SSMS.
Log in as AAD user
The user is now able to log in, just a bit different though. Connect to the database in SSMS and click on the Options>> button.
Type the name of the database and click connect.
You’re in! Good luck.