[QUESTION]

[aidelia]

Which component is your question about?

Collectors

Performance Monitor Version

1.2.1

Is your question about how it works, or the results?

How the tool works

What's your question?

I couldn't find the LOGIN/USER definition for using Perf Mon without a sysadmin login (in other words a read only login). So far I managed to create a LOGIN/USER with the script below, but it didn't work in Azure SQL and in regular installs has issues with Jobs. Is it possible to add this information to the documentation?

The script is:

`USE [master]
GO
CREATE LOGIN [SQLServerPerfMon] WITH PASSWORD=N'xxxxxx', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

GRANT VIEW DATABASE STATE TO [SQLServerPerfMon]
GRANT VIEW SERVER STATE TO [SQLServerPerfMon]

USE [msdb]

CREATE USER [SQLServerPerfMon] FOR LOGIN [SQLServerPerfMon]
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [SQLServerPerfMon]
--added after reading job collector errors, still not working
GRANT SELECT ON dbo.sysjobhistory TO [SQLServerPerfMon]
GRANT SELECT ON dbo.syssessions TO [SQLServerPerfMon]

GO
`

Additional Context

No response

[erikdarlingdata]

Thanks for the question! You're right that we don't have a dedicated doc for least-privilege setup yet — that's a gap we should fill.

Here's what the collectors actually need:

Server-Level (required)

GRANT VIEW SERVER STATE TO [SQLServerPerfMon];

This covers all DMV access (wait stats, query stats, memory, CPU, file I/O, extended events, etc.).

PerformanceMonitor Database (Full Dashboard only)

USE [PerformanceMonitor];
CREATE USER [SQLServerPerfMon] FOR LOGIN [SQLServerPerfMon];
ALTER ROLE [db_owner] ADD MEMBER [SQLServerPerfMon];

The collectors need to create/alter tables, insert data, and execute procedures. db_owner on the PerformanceMonitor database is the cleanest way to handle this. This is not the same as sysadmin — it's scoped to just that one database.

msdb (for SQL Agent job monitoring)

USE [msdb];
CREATE USER [SQLServerPerfMon] FOR LOGIN [SQLServerPerfMon];
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [SQLServerPerfMon];

SQLAgentReaderRole should cover sysjobs, sysjobactivity, sysjobhistory, and syssessions. If you're still getting errors, explicit grants like you had should work too.

Optional (gracefully skipped if missing)

• ALTER SETTINGS — installer tries to set blocked process threshold via sp_configure. Skipped with a warning if the login can't do it.
• Default trace — needs ALTER TRACE permission. Collector skips if unavailable.
• DBCC TRACESTATUS — server config collector skips trace flag detection if denied.

For Lite (connecting to remote servers)

Lite is simpler — no PerformanceMonitor database is created on the target server. You just need:

GRANT VIEW SERVER STATE TO [SQLServerPerfMon];
-- Plus msdb access if you want job monitoring

Azure SQL Database

Azure SQL DB doesn't support VIEW SERVER STATE the same way — you need VIEW DATABASE STATE on each database. SQL Agent isn't available, so msdb grants aren't needed. We detect Azure automatically and skip unsupported collectors.

TL;DR minimum script (on-prem, Full Dashboard)

USE [master];
CREATE LOGIN [SQLServerPerfMon] WITH PASSWORD = N'YourStrongPassword';
GRANT VIEW SERVER STATE TO [SQLServerPerfMon];

USE [PerformanceMonitor];
CREATE USER [SQLServerPerfMon] FOR LOGIN [SQLServerPerfMon];
ALTER ROLE [db_owner] ADD MEMBER [SQLServerPerfMon];

USE [msdb];
CREATE USER [SQLServerPerfMon] FOR LOGIN [SQLServerPerfMon];
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [SQLServerPerfMon];

We'll add this to the docs. What specific errors are you hitting on Azure SQL — is it Managed Instance or Azure SQL Database (single/elastic pool)?

[aidelia]

Regarding Azure is Azure SQL Database. In fact, I couldn't create a suitable LOGIN, so I not even reach the "Errors" in the collector health tab.

And sorry for not mentioned it, I'm just using Lite because of several IT constraints in my workshop.

[erikdarlingdata]

Good to know — Azure SQL Database is a different beast from on-prem or Managed Instance.

The login problem

Azure SQL Database doesn't support server-level logins the way on-prem does. Each database is isolated, so you create contained database users directly:

-- Connect to your target database (not master)
CREATE USER [SQLServerPerfMon] WITH PASSWORD = 'YourStrongPassword';
GRANT VIEW DATABASE STATE TO [SQLServerPerfMon];

That's it for Lite — VIEW DATABASE STATE covers the DMVs that Lite uses. There's no VIEW SERVER STATE on Azure SQL Database, and no msdb/SQL Agent.

Connecting in Lite

When you add the server in Lite, use the database-level credentials you just created:

• Server: yourserver.database.windows.net
• Database: yourdb (you must specify the database name)
• Login: SQLServerPerfMon
• Password: the one you set above
• Encrypt: Required (Azure enforces TLS)
• Trust Server Certificate: Off (Azure certs are from a trusted CA)

What works / doesn't work on Azure SQL DB

Lite auto-detects Azure SQL Database and adjusts:

• Works: Wait stats, query stats, memory, CPU, file I/O, active queries (via sp_WhoIsActive if deployed)
• Skipped automatically: SQL Agent jobs, default trace, trace flags, sp_configure — none of these exist on Azure SQL DB

Let us know if you hit any specific errors after creating the contained user!