Create a read-only user in Azure SQL

2020-08-21

_config.yml 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

Happy Coding! 😇