AppInsight for SQL Permissions

Version 1

    AppInsight for SQL Permissions

    The minimum SQL permissions required to use AppInsight for SQL are as

    follows:

    Must be a member of the db_datareader role on the msdb system database.

    Must have VIEW SERVER STATE permissions.

    View any definition.

    Connect permission to Master database.

    Execute permission on the Xp_readerrorlog stored procedure.

    Connect permission to the Msdb database.

    Must be member of db_datareader role in the Msdb database.

    Connect permission to all databases.

    Note: AppInsight for SQL supports both the SNMP and WMI protocols and uses

    SQL to gather information about the application. Additional information is

    available for nodes managed via WMI.

    The following script will configure permissions:

     

    Use this below to create logins if does not exist.

    USE master

    Create Login AppInsightUser

    GRANT VIEW SERVER STATE TO AppInsightUser

    GRANT VIEW ANY DEFINITION TO AppInsightUser

    EXEC sp_adduser @loginame = 'AppInsightUser' ,@name_in_db = 'AppInsightUser'

    GRANT EXECUTE ON xp_readerrorlog TO AppInsightUser USE msdb

    EXEC sp_adduser @loginame = 'AppInsightUser' ,@name_in_db = 'AppInsightUser'

    EXEC sp_addrolemember N'db_datareader',N'AppInsightUser'

    EXECUTE sp_MSforeachdb 'USE [?]; EXEC sp_adduser

    @loginame = ''AppInsightUser'', @name_in_db =''AppInsightUser'''

     

     

    For Domain User

     

    Please note the ID should be part of Local admin. I was not able to connect until I gave local admin access on the DB server. I Could not see anywhere in the document mentioned about it.

     

    USE master
    GRANT VIEW SERVER STATE TO "Domain\AppInsightUser"
    GRANT VIEW ANY DEFINITION TO "Domain\AppInsightUser"
    EXEC sp_adduser @loginame = 'Domain\AppInsightUser' ,@name_in_db = 'Domain\AppInsightUser'
    GRANT EXECUTE ON xp_readerrorlog TO "Domain\AppInsightUser"
    USE msdb
    EXEC sp_adduser @loginame = 'Domain\AppInsightUser' ,@name_in_db = 'Domain\AppInsightUser'
    EXEC sp_addrolemember N'db_datareader', N'Domain\AppInsightUser'
    EXECUTE sp_MSforeachdb 'USE [?]; EXEC sp_adduser @loginame = ''Domain\AppInsightUser'', @name_in_db = ''Domain\AppInsightUser'''