Is there a way to configure an Azure sql database as read-only temporarily. In my attempt to create a readonly user, I've ran the following
CREATE LOGIN reader WITH password='****' -- successful
CREATE USER readerUser FROM LOGIN reader; -- successful
EXEC sp_addrolemember 'db_datareader', 'readerUser'; -- failed
and the last statement failed with the error
Cannot alter the role 'db_datareader', because it does not exist or you do not have permission.
If you really wanted to add a read only user with the commands that you have mentioned above (so that you can connect in read-only mode or read-write mode depending on which user is specified for connection), here is an explanation of what probably went wrong, when you executed the above mentioned commands.
CREATE LOGIN reader WITH password='****' -- successful
CREATE USER readerUser FROM LOGIN reader; -- successful
EXEC sp_addrolemember 'db_datareader', 'readerUser'; -- failed
Cannot alter the role 'db_datareader', because it does not exist or you do not have permission.
I was able to reproduce the same error as you got when I executed all 3 commands that you mentioned directly on the master database.
Ideally, to create a read-only user, you have to create the login using master database, so that it's server level login, and then user creation and role member addition will happen on the data database that you intend to use.
-- Run this command on Master Database
CREATE LOGIN reader WITH password='****'
-- Run these commands on data Database
CREATE USER readerUser FROM LOGIN reader;
EXEC sp_addrolemember 'db_datareader', 'readerUser';
-- Optionally, run an extra command to create the same user on Master database. This command is not required if you connect only from code/api's, but if you want to connect to your database using SQL Management Studio, then you need this one.
CREATE USER readerUser FROM LOGIN reader; -- run this on Master Database again
