A least-privileged user (LPU) with VIEW DATABASE STATE and VIEW DEFINITION privileges is required for each user database monitored by the Management Pack for Microsoft Azure.

Create one of the following LPUs:

  • Basic Least-Privileged User (LPU)
  • Global Least-Privileged User (LPU) (for Geo-replicated databases)
Note: When using the LPU (or any custom user) to collect from any databases below the Premium pricing tier (i.e., Standard/Basic), certain properties, events, and metrics are not available. For details, see: Metrics (Microsoft Azure SQL).

Basic Least-Privileged User (LPU)

To create a basic LPU, perform the following tasks:

  • Create a Basic LPU
  • Grant privileges to the Basic LPU

Create a Basic LPU

On the "master" Microsoft Azure database instance, execute the following commands:

CREATE LOGIN lpu

WITH PASSWORD = <PASSWORD>;

Note: Replace <PASSWORD> with your own password.

Grant privileges to the Basic LPU

On each Microsoft Azure database, including the master, execute the following commands:

CREATE USER lpu FROM LOGIN lpu;

GRANT VIEW DATABASE STATE TO lpu;

GRANT VIEW DEFINITION TO lpu;

Global Least-Privileged User (LPU)

To create a globally-replicated LPU, complete the following tasks:

  • Create a Global LPU
  • Grant Privileges to the Global LPU
  • Create User with Specific SID
  • Grant Privileges for each Database on the Server

Create a Global LPU

On the first Microsoft Azure SQL database server, execute the following commands:

CREATE LOGIN globalLPU

WITH PASSWORD = <PASSWORD>;

Note: Replace <PASSWORD> with your own password.

Next, record the SID of the newly created user account by executing the following commands:

SELECT [name], [sid]

FROM [sys].[database_principals]

WHERE [type_desc] = 'SQL_USER' AND

[name] = 'globalLPU'

Grant Privileges to the Global LPU

Grant permissions to the globalLPU user on each database of the first server, including the master, by executing the following commands:

CREATE USER globalLPU FROM LOGIN globalLPU;

GRANT VIEW DATABASE STATE TO globalLPU;

GRANT VIEW DEFINITION TO globalLPU;

Create User with Specific SID

On the other servers, create a user with that specific SID by executing the following commands:

CREATE LOGIN globalLPU

WITH PASSWORD = <PASSWORD>,

SID = <Full SID here, should be log hex number starting with 0x>

Grant Privileges for each Database on the Server

For each database on the server, grant the appropriate permissions, including master, by executing the following commands:

CREATE USER globalLPU FROM LOGIN globalLPU;

GRANT VIEW DATABASE STATE TO globalLPU;

GRANT VIEW DEFINITION TO globalLPU;