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 it into something absolutely beautiful as shown below. Configuring the actual SQL permissions just went from awful and confusing to a copy/paste and a click.

Overview:

1. Create a domain user/service account in Active Directory
2. Run the script on your SQL instance. (Previously this was 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).

1) Create a domain account in Active Directory:

May I suggest: “SCOMSQLMONITOR”

2) Run the script on your SQL instance:

Setting SQL permissions would ordinarily be the awful part. Copy the script from below or download here.

-- Configure SQL Discovery/Monitoring Permissions
SET NOCOUNT ON

--DECLARE VARIABLES
DECLARE @accountname nvarchar(128)
DECLARE @command1 nvarchar(MAX)
DECLARE @command2 nvarchar(MAX)
DECLARE @command3 nvarchar(MAX)

--ENTER DOMAIN ACCOUNT HERE
SET @accountname = 'CONTOSO\SCOMSQLMONITOR'

--CREATE LOGIN
SET @command1 = 'USE [master];
IF NOT EXISTS (SELECT [name] FROM sys.server_principals WHERE [name] = ''' + @accountname +''')
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] + '];
IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '''+@accountname+''')
CREATE USER ['+@accountname+'] FOR LOGIN ['+@accountname+'];
'
FROM sys.databases WHERE database_id <> 2 and state = 0 AND DATABASEPROPERTYEX([name], 'Updateability') = N'READ_WRITE'

--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+'];
GRANT SELECT ON sys.database_mirroring_witnesses TO ['+@accountname+'];

USE [msdb];
EXEC sp_addrolemember @rolename=''PolicyAdministratorRole'', @membername='''+@accountname+''';
EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+''';
'

PRINT @command1;
PRINT @command2;
PRINT @command3;

--EXECUTE sp_executesql @command1;
--EXECUTE sp_executesql @command2;
--EXECUTE sp_executesql @command3;

(SQL converted to HTML here)

This script is easy to use. It really doesn’t get much easier than this. This will configure ALL of your databases (except TempDB) with “least privileges” for your designated RunAs account to discover and monitor.

Open a New Query window on your targeted SQL instance. Paste the query text. Enter YOUR account name on line 12,  then Execute (F5).

If you first want to see what the query will actually do, simply comment out the EXECUTE statements and UNcomment the PRINT statements. Then select the “Results To Text” icon in the toolbar:

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).

In my lab I have used the same account for both Discovery and Monitoring profiles. The screenshot below shows the Discovery profile only. I’ll not go into much detail of  the various SQL profiles because Kevin already covered it magnificently here.

Enjoy.

Let me know if you have any comments on how I can improve this article.

2017/8/1: SQL script updated per jalekz suggestions. Thanks!