CODEWITHSUNDEEP
sql-serverazureazure-sql-database
Adilson de Almeida Jr
Adilson de Almeida Jr

Reputation: 2755

Security roles on master database in Azure SQL

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?

Upvotes: 1

Views: 3151

Answers (1)

user272735
user272735

Reputation: 10648

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.

See also:

Upvotes: 1

Related Questions