What permissions are needed for a SQL user to run the sqlserver probe?

book

Article ID: 34659

calendar_today

Updated On:

Products

DX Infrastructure Management NIMSOFT PROBES

Issue/Introduction

What permissions are needed for a SQL user to run the sqlserver probe?

Environment

UIM 8.x or later

Resolution

The best practice to ensure that the probe can run without any permission issues is to use the "sa" user or equivalent.

If this is not possible, then at minimum, the user configured for the probe needs sufficient access

to read the following tables:

master.dbo.spt_values
master.dbo.sysdatabases
@dbname +'].dbo.sysfiles
@dbname +'].dbo.sysindexes
master.dbo.sysperfinfo
@dbname+'..sysobjects
master.dbo.syscurconfigs
master.dbo.sysprocesses

For the 'scan_density' and 'logic_fragment' checkpoints, the probe uses the  'dbcc showconfig' command and therefore it needs sysadmin, db_owner or db_ddladmin permission.

Remember that for each new database configured on the server/in the probe, you will need to ensure the user configured has the appropriate permissions.

The following SQL script can be used to assign the required permissions to a user.


-- START
USE master
GO
CREATE LOGIN <login_name> with password=N'<password>'
GO
sp_MSforeachdb 'USE [?] CREATE USER <user_name> for login <login_name>'
GRANT VIEW SERVER STATE TO <user_name>
GO
GRANT SELECT ON master.dbo.sysperfinfo TO <user_name>
GO
GRANT SELECT ON master.sys.databases TO <user_name>
GO
sp_MSforeachdb 'GRANT SELECT ON [?].sys.database_files TO <user_name> GRANT SELECT ON [?].sys.partitions TO <user_name> GRANT SELECT ON [?].sys.allocation_units TO <user_name> GRANT SELECT ON [?].sys.internal_tables TO <user_name> GRANT SELECT ON [?].sys.filegroups TO <user_name>'
GO

-- END

Additional Information

https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/ca-unified-infrastructure-management-probes/GA/alphabetical-probe-articles/sqlserver-sql-server-monitoring/sqlserver-sql-server-monitoring-release-notes.html