Create Read Only User in Azure DB

I have been struggling with creating a user who has read only access to an azure database and finally figured it out. The key is connecting to the right database in a new session when executing the t-sql commands. This is because you can not execute a “use” statement, change the database in the current session via the GUI, and you can not make named references to databases in your t-sql.

server_name.database_name.schema_name.object_name

Transact-SQL

1

2

3

4

5

6

7

8

9

--OPEN NEW SESSION TO MASTER DB

CREATELOGINReadOnlyLogin

WITHPASSWORD='ENTER STRONG PASSWORD'

;

CREATEUSERReadOnlyUser

FORLOGINReadOnlyLogin

WITHDEFAULT_SCHEMA=dbo

;

Transact-SQL

1

2

3

--OPEN NEW SESSION TO YOUR AZURE DB

CREATEUSERReadOnlyUserFROMLOGINReadOnlyLogin;

EXECsp_addrolemember'db_datareader','ReadOnlyUser';

Validate user has read only permissions by connecting to database using these new credentials and executing something like “CREATE TABLE dbo.test (test datetime);”, which should return an error message indicating permission denied.