Insights SQL Permission requirements
Advanced Insights ConfigMgr SQL Permissions requirements
Applies to: Patch My PC Advanced and Patch Insights
Advanced Insights needs read access to the ConfigMgr SQL database. If Advanced Insights is installed on a server that is not the ConfigMgr site server, or a custom Active Directory account is used for the IIS application pools, you will need to grant some SQL permissions.
Assigning Rights to ConfigMgr SQL
Option 1 - Using the Advanced Insights Computer Account
Open SQL Management Studio and connect to the required SQL instance for your ConfigMgr database
Execute the following script replacing the domain\computername and CM_XXX database name
Use Master
DECLARE @ADVINSTSERVERNAME AS VARCHAR(100)
DECLARE @CMDBNAME AS VARCHAR(100)
SET @ADVINSTSERVERNAME ='domain\computername$' -- Replace 'domain\computername$' - Example 'contoso\sccmsqlsvr$'
SET @CMDBNAME ='CM_XXX' -- Replace with the name of your ConfigMgr database name
DECLARE @LOGIN_nonquoted AS VARCHAR(100) = @ADVINSTSERVERNAME
DECLARE @LOGIN AS VARCHAR(100) = QUOTENAME(@LOGIN_nonquoted)
DECLARE @CMDBNAME_nonquoted AS VARCHAR(100) = @CMDBNAME
EXEC ('CREATE LOGIN ' + @LOGIN + ' FROM WINDOWS');
EXEC ('USE' +' '+@CMDBNAME+';' + 'CREATE USER ' + @LOGIN + ' FOR LOGIN ' + @LOGIN);
EXEC ('USE' +' '+@CMDBNAME+';' + 'ALTER ROLE db_datareader ADD MEMBER' + @LOGIN)
Option 2 - Using a user account for Advanced Insights
Advanced Insights access to the Configuration Manager SQL database can be configured to use a Active Directory user account. This account is set as the IIS application pool identity.
See: Advanced Insights IIS Application Pool Identity
Open SQL Management Studio and connect to the required SQL instance for your ConfigMgr database
Execute the following script replacing the domain\username and CM_XXX database name
Use Master
DECLARE @ADVINSTSERVERNAME AS VARCHAR(100)
DECLARE @CMDBNAME AS VARCHAR(100)
SET @ADVINSTSERVERNAME ='domain\username' -- Replace 'domain\username' - Example 'contoso\john'
SET @CMDBNAME ='CM_XXX' -- Replace with the name of your ConfigMgr database name
DECLARE @LOGIN_nonquoted AS VARCHAR(100) = @ADVINSTSERVERNAME
DECLARE @LOGIN AS VARCHAR(100) = QUOTENAME(@LOGIN_nonquoted)
DECLARE @CMDBNAME_nonquoted AS VARCHAR(100) = @CMDBNAME
EXEC ('CREATE LOGIN ' + @LOGIN + ' FROM WINDOWS');
EXEC ('USE' +' '+@CMDBNAME+';' + 'CREATE USER ' + @LOGIN + ' FOR LOGIN ' + @LOGIN);
EXEC ('USE' +' '+@CMDBNAME+';' + 'ALTER ROLE db_datareader ADD MEMBER' + @LOGIN)
Last updated
Was this helpful?