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

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)

Issue/Introduction

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

Environment

UIM 8.x or later

Component: UIMSQLserver

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

sqlserver (SQL Server Monitoring) Release Notes

 

Access prerequisites