It’s not practical to have everyone set up as an admin user, so what I’ll look at now is creating an ordinary Azure Active Directory user. I’ll start by creating another database on the same server with the imaginative name AnotherDb. Nothing special here, exactly the same type of database, so I will just fast forward through the creation.

The first thing I’ll do is log in as hallamad, and if you remember, was the admin, but I changed it to lorrylog via the Azure Active Directory SQL admin group. As we would expect when trying to log in with Azure Active Directory password, we cannot.

I’ll now log back in as the SQL admin and go to the new AnotherDb database, and create the hallamad user within that database. This is what’s called a contained user. That means the user only exists within that database and can only access that database on the server.

I’ll use the from external provider keywords to associate the database user with the Azure Active Directory user. We can see the hallamad user under AnotherDb’s users. To access the database, hallamad will need to be a member of the DB_data reader role, and I’ll give the user that role with the sp_add role member stored procedure.

If I try to connect to aadauth using the default database that is not specifying AnotherDb, the connection fails. When selecting AnotherDb is the default database, I can connect with hallamad, and I only have access to the AnotherDb. If I try to create a new table, I’m unable to as I only have read permission.

I’ll go back over to the other query window where I’m still logged in as lorrylog the database admin, and I’ll add hallamad to the DB_owner role. Now when I go back to the hallamad connection, I’m able to create the table.

Now let’s look at a variation on that theme by creating an Active Directory user from scratch. I’ll make a user called HallamSql with the user’s role, and I’ll join them to a group I’ve created called AnotherDbAccessGroup. There is nothing special about this group; it has no special access or roles assigned to it.

This time I will log in to AnotherDb using Active Directory universal with MFA multifactor authentication. The Azure login pops up for me to enter a password for HallamSql. As this is the first time logging in, I will have to create a new password.

It comes as no surprise I’m unable to connect because the Azure SQL Server doesn’t know anything about HallamSql. I’ll log in as lorrylog SQL admin and create a user, AnotherDbAccessGroup from external provider within the AnotherDb database. AnotherDbAccessGroup is the name of the Azure Active Directory group we just created, so this SQL Server user will be associated with that Azure AD Identity.

I’ll assign this user the DB_data reader role. Instead of using the SP_add role member procedure, I’ll use the alter role statement for no reason other than to show you there is more than one way to assign roles. 

Now when I log in with HallamSql, I can connect. I didn’t have to enter my password again, as my credentials have already been cached from the first attempt. It was only once I’d been verified with Azure Active Directory that I could not make the connection to the database server. 

Once again, HallamSql is a contained user because the login belongs to the user group, which is itself a contained user. If we look underneath the user’s node, we can see the user group name rather than HallamSql. One last thing before we move on is how to remove a user from a role. 

So far, we have just been adding users to roles, but what if you want to remove a user’s ability to perform some task. Well, it’s easy enough; instead of alter role add member, we use alter role drop member, which follows the format of other DDL drop commands.