![Jon Culp [.com]](http://jonculp.com/wp-content/uploads/2017/11/phoenix_wjon3.jpg)
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
|
1 2 3 4 5 6 7 8 9 |
--OPEN NEW SESSION TO MASTER DB CREATE LOGIN ReadOnlyLogin WITH PASSWORD = 'ENTER STRONG PASSWORD' ; CREATE USER ReadOnlyUser FOR LOGIN ReadOnlyLogin WITH DEFAULT_SCHEMA = dbo ; |
|
1 2 3 |
--OPEN NEW SESSION TO YOUR AZURE DB CREATE USER ReadOnlyUser FROM LOGIN ReadOnlyLogin; EXEC sp_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.
More information on managing database access and login security in azure: https://azure.microsoft.com/en-us/documentation/articles/sql-database-manage-logins/
