See full article here: : https://blogs.msdn.microsoft.com/tysonpaul/2016/04/09/sql-run-as-acc…scom-made-easy/

SQL RunAs account configuration is one of those SCOM topics that seem to be more on the tricky side of things. There are a number of articles hanging around the "innerwebs" that provide some level of instruction on ways that one might go about it. Kevin Holman offers an excellent article that explains much of what you would want to know about RunAs accounts and security profiles. He really goes into depth on how these things work. There are also some hints mentioned here. However, after searching around there don't seem to be any clear instructions on how one might configure an account to have the least privileges needed to correctly discover and monitor SQL components on a SQL server; a very common request in my field.

Question: How can you configure SQL Server permissions for a "least privileged" scenario?

Even Better Question: How can you configure SQL Server permissions for a "least privileged" scenario in just seconds?

The first part of the answer is easy, you pester your buddy Kyle (Microsoft Premier Services Field Engineer and SQL expert), asking again and again and again and again for weeks to write a script for you. When he sends you a half-baked mess transcribed from a cocktail napkin, you test it, finding the weak spots. Then badger him again and again and again until he turns into into something absolutely beautiful as shown below.

Overview:

  1. Create a domain user/service account in Active Directory
  2. Run the script on your SQL instance. (This is usually the hardest part but now takes only seconds)
  3. Create the Windows account credential in the SCOM Console.
  4. Distribute the account to the correct SQL servers.
  5. Add the account to the correct SQL profile(s) (Discovery and/or Monitoring and/or Default).
  6. Go make a sandwich.

See full article here: : https://blogs.msdn.microsoft.com/tysonpaul/2016/04/09/sql-run-as-acc…scom-made-easy/

2017/8/4: Updated script. 

SQL
Edit|Remove
-- Configure SQL Discovery/Monitoring Permissions 
 
SET NOCOUNT ON 
 
--DECLARE VARIABLES  
DECLARE @accountname nvarchar(128DECLARE @command1 nvarchar(MAXDECLARE @command2 nvarchar(MAXDECLARE @command3 nvarchar(MAX) 
 
--ENTER DOMAIN ACCOUNT HERE 
SET @accountname = 'CONTOSO\SCOMSQLMONITOR' 
 
--CREATE LOGIN 
SET @command1 = 'USE [master]; 
CREATE LOGIN ['+@accountname+'FROM WINDOWS WITH DEFAULT_DATABASE=[master]; 
' 
 
--CREATE A USER FOR THE LOGIN IN EACH DATABASE 
SET @command2 = '' 
SELECT @command2 = @command2 +  
' 
USE ['+name+']; 
CREATE USER ['+@accountname+'FOR LOGIN ['+@accountname+']; 
' 
FROM sys.databases WHERE database_id <> 2; 
 
--GRANT REQUIRED PERMISSIONS TO THE LOGIN IN EACH DATABASE 
SET @command3 =  
' 
USE [master]; 
GRANT VIEW ANY DATABASE TO ['+@accountname+']; 
GRANT VIEW ANY DEFINITION TO ['+@accountname+']; 
GRANT VIEW SERVER STATE TO ['+@accountname+']; 
USE [msdb]; 
EXEC sp_addrolemember @rolename=''PolicyAdministratorRole'', @membername='''+@accountname+'''EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+'''; 
' 
--PRINT AND/OR EXECUTE THE COMMANDS. 
--UNCOMMENT BELOW TO PRINT QUERYUSE "RESULTS TO TEXT" OPTION. 
 
-- PRINT @command1; 
-- PRINT @command2; 
-- PRINT @command3; 
 
EXECUTE sp_executesql @command1EXECUTE sp_executesql @command2EXECUTE sp_executesql @command3