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

Document ID : KB000034659
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

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

 

Answer:

 

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