How to assign SPN access to a Azure SQL Database using T-SQL

Ayush Shrivastava 0

I already have a SPN and I want to give it access to Azure SQL Database using T-SQL. How to do this.

Oury Ba-MSFT 17,791 Reputation points Microsoft Employee

2024-07-16T17:29:06.3133333+00:00
@Ayush Shrivastava Thank you for reaching out.

UserPrincipalName of the Microsoft Entra object for Microsoft Entra Users.

CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;

CREATE USER [alice@fabrikam.onmicrosoft.com] FROM EXTERNAL PROVIDER;

When creating the user in the Azure SQL database, the login_name must correspond to an existing Microsoft Entra login, or else using the FROM EXTERNAL PROVIDER clause will only create a Microsoft Entra user without a login in the master database.

Microsoft Entra server principals (logins) introduces creating users that are mapped to Microsoft Entra logins in the virtual master database. CREATE USER [bob@contoso.com] FROM LOGIN [bob@contoso.com]