Security for Sentry Monitoring ID on SQL Servers

mattscheperle

I understand a lot of documentation online has so far stated sysadmin, control server, and agent operator for SQL Server to be monitored. I also have the monitoring ID on the local server Administrator group.

So, my question would be are there any scripts that could apply something under sysadmin or what securables I could deny. Such as deny impersonate.

Here is an example but I still get; "SQL Server XEvent Trace Server Error: An error has occurred: Message: Remote Object Installation Failed" on Top SQL and Deadlocks.

This example is close to what I would like. Going with least privileges.

--grant privileges to login

USE [master]
GRANT VIEW SERVER PERFORMANCE STATE TO [domain\LoginID]
GRANT VIEW SERVER STATE TO [domain\LoginID]
GRANT VIEW ANY DATABASE TO [domain\LoginID]
GRANT VIEW ANY DEFINITION TO [domain\LoginID]
GRANT ALTER ANY EVENT SESSION TO [domain\LoginID]
GRANT CONNECT ANY DATABASE TO [domain\LoginID]

--grant privileges to user on master database

GRANT EXECUTE ON OBJECT::xp_enumerrorlogs TO [domain\LoginID]
GRANT EXECUTE ON OBJECT::xp_readerrorlog TO [domain\LoginID]

--grant privileges to user on master database for Windows authentication

GRANT EXECUTE ON OBJECT::xp_logininfo TO [domain\LoginID]

-- grant control user but deny impersonate
use [master]
GO
GRANT CONTROL SERVER TO [domain\LoginID]
GO
use [master]
GO
DENY IMPERSONATE ANY LOGIN TO [domain\LoginID]
GO

-- Temp DB permissions on server being monitored.
use [tempdb]
GO
GRANT CREATE SCHEMA TO [domain\LoginID]
GO
use [tempdb]
GO
GRANT CREATE TABLE TO [domain\LoginID]
GO
use [tempdb]
GO
GRANT VIEW DATABASE STATE TO [domain\LoginID]
GO

--msdb permissions for monitoring

USE [msdb]
GO
ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [domain\LoginID]
GO

--grant privileges to user on msdb database

USE [msdb]
GRANT SELECT ON msdb.dbo.sysjobs TO [domain\LoginID]
GRANT SELECT ON msdb.dbo.sysjobhistory TO [domain\LoginID]
GRANT SELECT ON msdb.dbo.syssessions TO [domain\LoginID]
GRANT SELECT ON msdb.dbo.sysjobactivity TO [domain\LoginID]
GRANT EXECUTE ON msdb.dbo.agent_datetime TO [domain\LoginID]

-- For SQL Server 2022 I Add some of the below to see how things perform

use [master]
GO
GRANT VIEW ANY PERFORMANCE DEFINITION TO [domain\LoginID]
GO
GRANT VIEW ANY SECURITY DEFINITION TO [domain\LoginID]

mattscheperle

I think I figured something out. I still had my script above for one of our servers so had that run. Then, yesterday added sysadmin. So top sql and other items showed up.

Just now I took off the sysadmin. So far no errors. Maybe it just needed to create some objects/schemas and jobs then I was able to remove sysadmin and keep the script below applied.

Although it still is fairly elevated with control server.

I did deny impersonate. We have some FTI servers that will need stricter security though and I would like to see if anyone else has done something similar. Next test I may take off the control server.