Security roles on master database in Azure SQL

Question

I´m building an Azure SQL resource monitor, and I have to grant permission for a login to access the 'sys.resource_stats' on the master database of a Azure SQL 'server'. I can´t use neither loginmanager or dbmanager roles, because they grant some permission I don´t want to grant for a monitor application.

Is there another database role on the master database which i can add members?

score 1 · Accepted Answer · edited May 23 '17 at 11:57

I have recently created a special monitoring login/database user (see below) and I didn't find any reason to assign roles (I'm Azure noob thought).

Step 1

Create a login and a database user in master database:

-- in master database
-- create a login
create login <LOGIN> with password = '<PASSWORD>'
-- create a corresponding database user
create user <USER> from login <LOGIN>;

Step 2

Authorize the login to use a target database:

-- in target database
-- create a corresponding database user
create user <USER> from login <LOGIN>;
-- grant permission to view dynamic management views
grant view database state to <USER>;

In practice <LOGIN> and <USER> are the same, like foo_monitor.

Security roles on master database in Azure SQL

1 Answers1