Page History
...
SQL Diagnostic Manager The following documentation allows you to monitor SQL Server instances without SysAdmin rights. However, these lowered permissions limit the availability of data.
The user creation scripts are detailed, as follows:
create a user with the necessary permissions for SQL Diagnostic Manager, allowing the new user to gather diagnostic data from a monitored instance successfully.
Anchor | ||||
---|---|---|---|---|
|
SQL Diagnostic Manager also includes a SQL script Follow the next steps to create a new user with minimum recommended permissions necessary for the product SQL Diagnostic Manager to function.
- Use the Find/Replace function to locate and replace all references of 'SQLdmCollectionUser' with the SQL user that is to be used for monitoring the SQL Server instance.
- Connect to the SQL Server that you want to monitor with the Admin user and run this script to create the SQL user with the recommended permissions to view the data on most of the screens of the application.
Find the RecommendedPermissionsOnPremises.sql script below:Attachments preview false upload false old false patterns RecommendedPermissionsOnPremises.sql sortBy name page User Creation Scripts On Premises - When registering the instance in SQL Diagnostic Manager, specify the SQL account that was created through the script so that SQL Diagnostic Manager connects to the monitored instance using the newly created account.
...
title | Recommended Permissions |
---|
- Open the script with the source code editor of your preference.
- Find and replace all the following instances:
-
'$SQLdmMonitorRole'
with the desired name of the custom server role. '$username'
with the account name that SQLDM will use to collect diagnostic data. You may use a SQL account (e.g.SQLdmService
) or a domain account (e.g.DOMAIN\SQLdmService
).'$password'
with the password related to the account. Only if you are using a SQL account.
Tip Use the Find/Replace function to locate and replace all references from the script.
-
Execute the script against the target monitored instance using an account with sysadmin rights. This means you should execute the script on each instance you intend to monitor with the user created.
Below you can find the script for user creation on-premises, also you can download it by clicking here.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
USE master
IF NOT EXISTS(SELECT [name] FROM [sys].[server_principals] WHERE [type]='R' AND [name]='$SQLdmMonitorRole')
CREATE SERVER ROLE [$SQLdmMonitorRole]
IF NOT EXISTS(SELECT [name] FROM [sys].[server_principals] WHERE ([type]=N'S' OR [type]=N'U') AND [name]='$username')
BEGIN TRY
IF CHARINDEX('\','$username') <> 0
CREATE LOGIN [$username] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
ELSE
CREATE LOGIN [$username] WITH PASSWORD=N'$password', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
, ERROR_SEVERITY() AS ErrorSeverity
, ERROR_STATE() AS ErrorState
, ERROR_PROCEDURE() AS ErrorProcedure
, ERROR_LINE() AS ErrorLine
, ERROR_MESSAGE() AS ErrorMessage;
RETURN;
END CATCH
IF NOT EXISTS(SELECT [name] FROM [sys].[database_principals] WHERE [type] = N'S' AND [name] ='$username')
CREATE USER [$username] FOR LOGIN [$username]
ALTER SERVER ROLE [$SQLdmMonitorRole] ADD MEMBER [$username]
GRANT EXECUTE on xp_loginconfig to [$username]
GRANT EXECUTE on xp_regread to [$username]
GRANT EXECUTE on xp_readerrorlog to [$username]
GRANT EXECUTE on xp_instance_regread to [$username]
GRANT EXECUTE on sp_OACreate to [$username]
-- GRANT SERVER LEVEL PERMISSIONS --
GRANT VIEW ANY DEFINITION TO [$SQLdmMonitorRole]
GRANT VIEW ANY DATABASE TO [$SQLdmMonitorRole]
GRANT VIEW SERVER STATE TO [$SQLdmMonitorRole]
GRANT ALTER SETTINGS TO [$SQLdmMonitorRole]
-- GRANT ADDITIONAL XEVENTS AND TRACE PERMISSIONS --
GRANT ALTER ANY EVENT SESSION TO [$SQLdmMonitorRole]
GRANT ALTER TRACE TO [$SQLdmMonitorRole]
GRANT CREATE ANY DATABASE TO [$SQLdmMonitorRole]
/** The following script adds the user to the database with the required permissions**/
SET @cmd = '
USE [?]
IF ((SELECT is_read_only FROM sys.databases WHERE name = DB_NAME()) = 0)
BEGIN
IF ((SELECT state FROM sys.databases WHERE name = DB_NAME())=0)
BEGIN
IF NOT EXISTS(SELECT [name] FROM [sys].[database_principals] WHERE ([type] = N''S'' OR [type] = N''U'') AND [name]=''$username'')
CREATE USER [$username] FOR LOGIN [$username]
ALTER ROLE [db_owner] ADD MEMBER [$username]
IF (DB_NAME()=''msdb'')
ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [$username]
IF (SELECT COUNT(*)FROM [sys].[database_mirroring] WHERE [mirroring_guid] IS NOT NULL) > 0
ALTER SERVER ROLE [dbm_monitor] ADD MEMBER [$SQLdmMonitorRole]
END
END'
EXEC sp_MSforeachdb @cmd
|
When running the script above, it is added:
- A new user (a Windows or a SQL Server account) to the SQL Server instance.
- A new SQL Server level role.
- The
db_owner
role to view Databases > Summary. - The
SQLAgentOperatorRole
role to themsdb
database. Which lets you view, start, or stop SQL Agent Jobs. - The
dbm_monitor
role, allowing you to monitor the database mirroring. Only required if there are any databases participating in mirroring.
Info | ||
---|---|---|
| ||
Restored databases onto the instance later must have the monitor account added as a bd_owner |
These are the recommended permissions on-premises:
GRANT SERVER LEVEL PERMISSIONS
/** Assign VIEW SERVER STATE Permissions to SQLdmCollectionUser **/
GRANT VIEW SERVER STATE TO [SQLdmCollectionUser]
/** Assign VIEW ANY DEFINITION Permissions to SQLdmCollectionUser **/
GRANT VIEW ANY DEFINITION TO [SQLdmCollectionUser]
/** Assign VIEW ANY DATABASE Permissions to SQLdmCollectionUser **/
GRANT VIEW ANY DATABASE TO [SQLdmCollectionUser]
GRANT ADDITIONAL XEVENTS AND TRACE PERMISSIONS
/** Assign ALTER TRACE Permissions to SQLdmCollectionUser **/
GRANT ALTER TRACE TO [SQLdmCollectionUser]
/** Assign ALTER ANY EVENT SESSION Permissions to SQLdmCollectionUser **/
GRANT ALTER ANY EVENT SESSION TO [SQLdmCollectionUser]
GRANT CREATE ANY DATABASE TO [SQLdmCollectionUser]
...
SQL Diagnostic Manager now includes a SQL script to create a new user with minimum permissions for the product to function.
...
Attachments | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
...
. |