AppInsight for SQL Permissions

amarnath_r

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'''

Find more posts tagged with

app insight for sql

content_exchange

sam template

server and application monitor

Accepted answers

All comments

aLTeReGo

I believe this is outlined in the KB article below.

SolarWinds Knowledge Base :: AppInsight for SQL Requirements and Permissions

amarnath_r

Hi Alterego,

Yes, The local admin rights part is missing in the KB so just wanted to add that

foonly

The SQL commands are documented in the SAM manuals, V6.2.x-6.2.4.

This local Administrator membership requirement is missing from all versions of the manual, including the latest V6.2.4 manual.

FormerMember

What if the user is a domain admin which is effectively a local admin on every Windows server in the domain?

m-milligan

sp_adduser and sp_addrolemember are both deprecated in newer versions of SQL Server. Newer versions need to use this instead:

USE master

GRANT VIEW SERVER STATE TO "Domain\AppInsightUser"

GRANT VIEW ANY DEFINITION TO "Domain\AppInsightUser"

CREATE USER 'Domain\AppInsightUser'

GRANT EXECUTE ON xp_readerrorlog TO "Domain\AppInsightUser"

USE msdb

CREATE USER 'Domain\AppInsightUser'

ALTER ROLE 'db_datareader' ADD MEMBER 'Domain\AppInsightUser'

EXECUTE sp_MSforeachdb 'USE [?]; CREATE USER ''Domain\AppInsightUser'''

bardeev

Thank you.

Can you write version of SQL Server where need to change permission?

2016 and later?

m-milligan

That script should also work in SQL Server 2016 and later.

gharrison

Here's a slightly modified version that I came up with, when trying to get AppInsight to connect to my SQL DBs, in case it's helpful to anyone:

USE master

CREATE LOGIN [Domain\AppInsightUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

USE master

CREATE USER [Domain\AppInsightUser] FROM LOGIN [Domain\AppInsightUser] WITH DEFAULT_SCHEMA = dbo

GRANT VIEW SERVER STATE TO "Domain\AppInsightUser"

GRANT VIEW ANY DEFINITION TO "Domain\AppInsightUser"

--GRANT VIEW ANY DATABASE TO "Domain\AppInsightUser" -- By default granted to PUBLIC

GRANT EXECUTE ON xp_readerrorlog TO "Domain\AppInsightUser"

GRANT CONNECT ANY DATABASE TO "Domain\AppInsightUser" -- This replaces the last line, and applies to future DBs as well

USE msdb

CREATE USER [Domain\AppInsightUser] FROM LOGIN [Domain\AppInsightUser] WITH DEFAULT_SCHEMA = dbo

EXEC sp_addrolemember N'db_datareader', N'Domain\AppInsightUser'

--EXECUTE sp_MSforeachdb 'USE [?]; CREATE USER ''Domain\AppInsightUser'''