How to create a read only user for a SQL Azure database

I am updating, or should I say, re-coding a website I have from ASP.NET Web Forms (aspx) to ASP.NET Core Razor Pages (chtml).  The database it connects to only has a global admin, full access user identity.  So I thought that I would fix that and create a need account for read only access.  This will also help because I am using Entity Framework Core and I have sometimes deleted databases when I run EF and I am simply scared to run my application for the first time with a full access account.  Looking around for some tips I quickly found out there was no support for this in the portal, it also looks like the desired approach is the use an Active Directory account, but this website and domain doesn’t have its own identities.  Plus, I wanted to do it quickly.

I was able to find some articles which stated I needed to execute these 3 queries.

 
CREATE LOGIN readonly WITH password='**********';
CREATE USER readonly FROM LOGIN readonly;
ALTER ROLE db_datareader ADD MEMBER readonly;

I thought I’d be smart and do it from within Visual Studio.  As seen in Figure 1, I added the database connection, right-clicked and selected New Query.  I wasn’t able to get the focus on the correct database so my effort and smartness results in an unsuccessful effort.

Figure 1, adding user to SQL Azure via Visual Studio

Instead I downloaded Microsoft SQL Server Management Studio, which gave me for overview of may databases and I was able to execute those queries and add the login, user and set the account to read only.  Figure 2 illustrates how the interface looked in Microsoft SQL Server Management Studio.

Figure 2, adding user to SQL Azure via Microsoft SQL Server Management Studio

I checked that the account was in the read only role by running this query, also seen in Figure 2.

 
SELECT DP1.name AS DatabaseRoleName,   
    isnull (DP2.name, 'No members') AS DatabaseUserName   
FROM sys.database_role_members AS DRM  
RIGHT OUTER JOIN sys.database_principals AS DP1  
    ON DRM.role_principal_id = DP1.principal_id  
LEFT OUTER JOIN sys.database_principals AS DP2  
    ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
ORDER BY DP1.name; 

I also logged into my database with the account and tried and INSERT and UPDATE and access was denied.  All good.

I am sure with more effort I could have gotten it to work via Visual Studio, but having used SQL Server Management Studio in the past I knew there would be no issue making it run from there.  I like both of these tools very much, platforms for creativity and a place you can change or impact the work from.  Rock!