Document the minimum required permissions for SQL Monitoring

[danbarua]

sysadmin seems to work! :)

But it would be nice to know the minimal permission set to give my OpServer service account.

[NickCraver]

The reason we haven't gotten to this is I'm far from the SQL feature set that I have in mind. As time allows, I'll add way more to the SQL section of Opserver. Integration with some of the tools already out there isn't far off, hoping the slower holiday season will let me spend some more time on that.

Once more of that is in place, we'll start seeing what permissions are needed for which features so you can choose. At that point, Opserver can intelligent enable or disable functionality, informing you of what permissions it lacks.

[waynebrantley]

Very impressive with the features set it already has!

[adutton]

This was what I used to create a SQL authentication login and grant it most of the SQL permissions it needed. This still fails because DBCC LOGINFO requires sysadmin level security, but it gives you most of the interesting details.

USE [master]
GO

CREATE LOGIN [opserver] WITH PASSWORD=N'correcthorsebatterystaple', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

GRANT VIEW SERVER STATE to opserver;
GRANT VIEW ANY DEFINITION TO opserver;

EXEC sp_MSforeachdb 'use [?]; CREATE USER opserver FOR LOGIN opserver;';

USE msdb;
GRANT SELECT ON sysjobs TO opserver;
GRANT SELECT ON sysjobhistory TO opserver;
GRANT SELECT ON sysjobactivity TO opserver;
GRANT SELECT ON syscategories TO opserver;
EXEC sp_addrolemember N'SQLAgentReaderRole', N'opserver'

[briantist]

This would really be great to have. It feels pretty wrong to use or create an account that has sysadmin rights on every SQL server for a dashboard. A breakdown of which permissions are needed for which features would be helpful in letting us make decisions about how much power the opserver account has.

Addressing it sooner, in my opinion, is easier than trying to figure it out later when more features are added; it should be thought of as new features are being developed. I guess I'm thinking of it in sort of a TDD mindset.

@adutton that's a great start, thanks for that!

[dgaspar]

This was what I used to create a SQL authentication login and grant it most of the SQL permissions it needed. This still fails because DBCC LOGINFO requires sysadmin level security, but it gives you most of the interesting details.

USE [master]
GO

CREATE LOGIN [opserver] WITH PASSWORD=N'correcthorsebatterystaple', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

GRANT VIEW SERVER STATE to opserver;
GRANT VIEW ANY DEFINITION TO opserver;

EXEC sp_MSforeachdb 'use [?]; CREATE USER opserver FOR LOGIN opserver;';

USE msdb;
GRANT SELECT ON sysjobs TO opserver;
GRANT SELECT ON sysjobhistory TO opserver;
GRANT SELECT ON sysjobactivity TO opserver;
GRANT SELECT ON syscategories TO opserver;
EXEC sp_addrolemember N'SQLAgentReaderRole', N'opserver'

The latest checkout also requires:

USE msdb;
GRANT EXECUTE ON agent_datetime TO opserver;
GRANT SELECT ON dbo.sysjobsteps TO opserver;

USE master;
GRANT EXECUTE ON sp_WhoIsActive TO opserver;

[0UserName]

@adutton, why do you need EXEC sp_MSforeachdb 'use [?]; CREATE USER opserver FOR LOGIN opserver;'; if only two database have some permission GRANT?