Question

A .NET application runs on an Azure App Service and connects to an Azure SQL Database. Security policy requires that no passwords or connection-string secrets be stored anywhere, and the application must authenticate to the database using its system-assigned managed identity with only the ability to read and write data in existing tables. As the DBA, which T-SQL statements should you run in the user database to enable this access following least privilege?

Accepted Answer

CREATE USER [myappservice] FROM EXTERNAL PROVIDER; then ALTER ROLE db_datareader ADD MEMBER [myappservice]; and ALTER ROLE db_datawriter ADD MEMBER [myappservice];

Answer

CREATE LOGIN [myappservice] WITH PASSWORD = '...'; then CREATE USER [myappservice] FROM LOGIN [myappservice]; and ALTER ROLE db_owner ADD MEMBER [myappservice];

Answer

CREATE USER [myappservice] WITH PASSWORD = '...'; then ALTER ROLE db_datareader ADD MEMBER [myappservice];

Answer

CREATE USER [myappservice] FROM EXTERNAL PROVIDER; then ALTER ROLE db_owner ADD MEMBER [myappservice];