SQL Server – Granting SQL Server permission to service SID

Introduction

We know that SQL Server itself is using it own SID NT SERVICEMSSQL$ and NT SERVICESQLAGENT$ to grant permission to the database engine and the SQL agent services.
But the same method can be used for other application services to acces and use the SQL Server instances. The enables to allow the access to the SQL instance when the service is running and avoid to use service account.
There are many application using services where this method can be interesting.
To illustrate how to implement it, I will use the System Center Operations Manager Health Service (SCOM) example as the monitoring is usually a typical case.

How to implement it?

The first thing to check is if an application has a SID. If this is not the case you have to create one.
To create a SID you can use the Windows SDK SC.EXE utility used for controlling a service (ref: Controlling a Service Using SC – Win32 apps | Microsoft Docs)

For my example you can create the SCOM Healthservice SID with the following PowerShell script:

sc.exe --% sidtype "HealthService" unrestricted

You can check the service SID using the following PowerShell script:

sc.exe --% qsidtype "HealthService"
sc showsid HealthService

It is as well important to set the [ServiceSidType] registry parameter to 1:

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesHealthService
ServiceSidType =1

When this is done you can use the SID to create its login in the SQL Server instance using the TSQL script:

CREATE LOGIN [NT SERVICEHealthService] FROM WINDOWS
GO

You can used than the login to grant the permission to the SQL Server instance directly as you do it for any other login.
For our SCOM Healthservice example you can set the following permissions your instance. Of course this is depending of your monitoring need and constraints:

USE [master]
SET NOCOUNT ON
/*User account that System Center Operations Manager will use to access
    Default is the Service SID for the HealthService*/
 
DECLARE @accountname sysname = 'NT SERVICEHealthService'
-- Create the server role and grant permissions
CREATE SERVER ROLE [SCOM_HealthService]
GRANT VIEW ANY DATABASE TO [SCOM_HealthService];
--GRANT ALTER ANY DATABASE TO [SCOM_HealthService];
GRANT VIEW ANY DEFINITION TO [SCOM_HealthService];
GRANT VIEW SERVER STATE TO [SCOM_HealthService];
GRANT EXECUTE ON sys.xp_readerrorlog TO [SCOM_HealthService];
 
USE [msdb];
GRANT SELECT ON [dbo].[sysjobschedules] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[sysschedules] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[sysjobs_view] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[syscategories] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[log_shipping_primary_databases] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[log_shipping_secondary_databases] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[log_shipping_monitor_history_detail] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[log_shipping_monitor_secondary] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[log_shipping_monitor_primary] TO [SCOM_HealthService];
GRANT EXECUTE ON [dbo].[sp_help_job] TO [SCOM_HealthService];
GRANT EXECUTE ON [dbo].[sp_help_jobactivity] TO [SCOM_HealthService];
GRANT EXECUTE ON [dbo].[SQLAGENT_SUSER_SNAME] TO [SCOM_HealthService];
EXEC sp_addrolemember @rolename='PolicyAdministratorRole', @membername='SCOM_HealthService';
EXEC sp_addrolemember @rolename='SQLAgentReaderRole', @membername='SCOM_HealthService';
 
 
USE [master]
DECLARE @createLoginCommand nvarchar(200)
SET @createLoginCommand = '
  CREATE LOGIN '+ QUOTENAME(@accountname) +
  ' FROM WINDOWS WITH DEFAULT_DATABASE=[master];'
EXEC(@createLoginCommand);
-- Add the login to the user-defined server role
EXEC sp_addsrvrolemember @loginame = @accountname
  , @rolename = 'SCOM_HealthService'
DECLARE @createDatabaseUserAndRole nvarchar(max)
SET @createDatabaseUserAndRole = '';
SELECT @createDatabaseUserAndRole = @createDatabaseUserAndRole + '
  USE ' + QUOTENAME(db.name) + ';
  CREATE USER ' + QUOTENAME(@accountname) +
  ' FOR LOGIN ' + QUOTENAME(@accountname) + ';
  CREATE ROLE [SCOM_HealthService];
  EXEC sp_addrolemember @rolename =
  ''SCOM_HealthService'', @membername
  = '+ QUOTENAME(@accountname) + ''
-- 'ALTER ROLE [SCOM_HealthService] ADD MEMBER '
  -- '+ QUOTENAME(@accountname) + ';'
FROM sys.databases db
LEFT JOIN sys.dm_hadr_availability_replica_states hadrstate ON
    db.replica_id = hadrstate.replica_id
WHERE db.database_id  2
    AND db.user_access = 0
    AND db.state = 0
    AND db.is_read_only = 0
    AND (hadrstate.role = 1 or hadrstate.role is null);
EXEC(@createDatabaseUserAndRole)
GO
GO

Conclusion

You can see that through this example that it is quite easy to grant permission to an application service SID and therefore avoid using the RUN AS service account. You can standardized the deployment of the service on your SQL Server instance in a much easier way without to take care of the service accounts used, especially if you use same service account for several services.