Create a read-only user in Azure SQL

One of my colleagues asked me how to create a read-only user for a particular database in Azure SQL, I thought it would be similar process as how I did in SQL server. As it turned out, it wasn’t really straightforward. I decide to share my learning in this blog post.

Step 1

Run the following scripts to create user (1st script) and grant db (2nd script)

CREATE USER [yourusername] WITH PASSWORD = 'strong password';

ALTER ROLE [db_datareader] ADD MEMBER [yourusername];

Step 2

Open your SQL Management Studio (SSMS). Fill the login details with the username/password created above, click on Options and enter the database name.

NOTES: You must always specify the database name as the user created with step 1 SQL script is using Contained Database User Model

References

• Contained Database Users - Making Your Database Portable
• Database-Level Roles
• Permissions (Database Engine)

Happy Coding! 😇