What is the SQL Azure equivalent of the server "public" role?

Question

In our on-premise database, we create database users mapped to a login, and then assign the user to specific roles within the database. Access to the database is strictly governed by which stored procedures each role has EXECUTE permissions on. The user has no other rights other than by stored procedure grants (e.g. they cannot run arbitrary select statements in SSMS).

I notice that SQL Server (on-premise) provides a default user assignment to the server's "public" role. When assigning user access to a SQL Azure server however, I do not see the "public" role. However, I must select a role in order to create the user:

Can anyone please advise what I should do here to honour our existing approach, or whether the on-premise approach is even still possible?

I cannot find any documentation to describe what privileges are granted from the roles listed.

Answer 1

After much experimentation, I stumbled upon an answer on the Secure your Azure SQL Database.

Please correct me if I am wrong here!

It is strange that the documentation on securing SQL Azure does not mention the following in any detail. The clues were here (emphasis mine):

SQL Authentication, which uses username and password for logins and users that are valid only in the context of a specific database within a logical server.

-snip-

By default, the user can connect to the database, but has no permissions to read or write data.

In SQL Azure, it is possible to have database-contained users with a password (which is different from the on-premise approach of creating a user against an existing login account). This makes sense, because the all-in-one contained approach allows for a seamless failover.

The solution is to use SSMS and not the Azure portal:

-- After connecting to SQL Azure within SSMS (as dbo)
CREATE USER [UserNameForApplication] WITH PASSWORD=N'543rg.....ehgdGDF';
GO
ALTER ROLE [MyExistingDatabaseRole] ADD MEMBER UserNameForApplication;
GO

My application can now access the SQL Azure database with zero additional rights that would otherwise be granted via the Azure portal.