How to create and check AAD Login in Azure SQL Database
If you want to create AAD login for Azure SQL Database firstly be aware that you have to create AAD users with Azure Active Directory Admin, That means you’re not able to create AAD Logins for your database when you use a SQL Login.
USE statement is not supported to switch between databases. Use a new connection to connect to a different database.
Check are you part of Azure Active Directory Admin?
Simple step, you need to go to the Portal then jump to SQL Server page and from Setting choose Azure Active Directory and see if your account is part of Azure Active Directory Admin. See the following screenshot:
Create Login in master database
Then you need go login to SSMS with AAD admin and run the following command in master to create the login.
Use master
CREATE LOGIN [xxx@microsoft.com] FROM EXTERNAL PROVIDER
GO
Check the user has been created?
One of the troubleshooting steps is run the following query in master to see if the login has been created or not, obviously you can add condition to see the specific user.
SELECT name, type_desc, type, is_disabled
FROM sys.server_principals
WHERE type_desc like 'external%'
That’s not relevant to this topic but for SQL Login you can use the following query:
Select name,type,type_desc,create_date,modify_date FROM sys.database_principals
WHERE type_desc = 'SQL_USER'
Create the user account
Next step you need to run the following command to create the user account
CREATE USER [xxx@microsoft.com] FROM LOGIN [xxx@microsoft.com]
Then you only need to give the user access to the object or anything you wish the following example I added to DBReader group and user can read objects from that database.
ALTER ROLE [db_datareader] ADD MEMBER [xxx@microsoft.com]
As I also explained here you might need to change the database name from the option as you do not have access to Master database.
Useful links
Two following links also be useful for creating AAD Login and SQL Login