Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

SQL Diagnostic Manager allows you to monitor SQL Server instances without SysAdmin rights. However, these lowered permissions limit the availability of data.

Anchor
onpremisestwo
onpremisestwo
Recommended permissions on-premises

SQL Diagnostic Manager also includes a SQL script to create a new user with minimum permissions necessary for the product to function.

  1. 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.
  2. 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
    previewfalse
    uploadfalse
    oldfalse
    patternsRecommendedPermissionsOnPremises.sql
    sortByname
    pageUser Creation Scripts On Premises
  3. 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.


Info
titleRecommended Permissions

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]

Anchor
onpremisesone
onpremisesone
Minimum permissions on-premises

SQL Diagnostic Manager now includes a SQL script to create a new user with minimum permissions for the product to function.

  1. 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.
  2. 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 minimum permissions required to view the data on most of the screens of the application.
    Find the MinimumPermissionsOnPremises.sql script below:
    Attachments
    uploadfalse
    oldfalse
    patternsMinimumPermissionsOnPremises.sql
    sortByname
    pageUser Creation Scripts On Premises
  3. 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.


Scroll PDF Exporter Ignore
Newtabfooter
aliasIDERA
urlhttp://www.idera.com
Newtabfooter
aliasProducts
urlhttps://www.idera.com/productssolutions/sqlserver
Newtabfooter
aliasPurchase
urlhttps://www.idera.com/buynow/onlinestore
|
Newtabfooter
aliasSupport
urlhttps://idera.secure.force.com/
|
Newtabfooter
aliasCommunity
urlhttp://community.idera.com
|
Newtabfooter
aliasResources
urlhttp://www.idera.com/resourcecentral
|
Newtabfooter
aliasAbout Us
urlhttp://www.idera.com/about/aboutus
|
Newtabfooter
aliasLegal
urlhttps://www.idera.com/legal/termsofuse