{"id":16854,"date":"2021-11-22T14:43:57","date_gmt":"2021-11-22T13:43:57","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-granting-sql-server-permission-to-service-sid\/"},"modified":"2021-11-22T14:43:57","modified_gmt":"2021-11-22T13:43:57","slug":"sql-server-granting-sql-server-permission-to-service-sid","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-granting-sql-server-permission-to-service-sid\/","title":{"rendered":"SQL Server – Granting SQL Server permission to service SID"},"content":{"rendered":"

Introduction<\/h2>\n

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.
\nBut 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.
\nThere are many application using services where this method can be interesting.
\nTo 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.<\/p>\n

How to implement it?<\/h2>\n

The first thing to check is if an application has a SID. If this is not the case you have to create one.
\nTo 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<\/a>)<\/p>\n

For my example you can create the SCOM Healthservice SID with the following PowerShell script:<\/p>\n

sc.exe --% sidtype \"HealthService\" unrestricted\n<\/pre>\n
You can check the service SID using the following PowerShell script:<\/p>\n
sc.exe --% qsidtype \"HealthService\"\nsc showsid HealthService\n<\/pre>\n
It is as well important to set the [ServiceSidType] registry parameter to 1:<\/p>\n
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesHealthService\nServiceSidType =1\n<\/pre>\n
When this is done you can use the SID to create its login in the SQL Server instance using the TSQL script:<\/p>\n
CREATE LOGIN [NT SERVICEHealthService] FROM WINDOWS\nGO\n<\/pre>\n
You can used than the login to grant the permission to the SQL Server instance directly as you do it for any other login.
\nFor our SCOM Healthservice example you can set the following permissions your instance. Of course this is depending of your monitoring need and constraints:<\/p>\n
USE [master]\nSET NOCOUNT ON\n\/*User account that System Center Operations Manager will use to access\n    Default is the Service SID for the HealthService*\/\n \nDECLARE @accountname sysname = 'NT SERVICEHealthService'\n-- Create the server role and grant permissions\nCREATE SERVER ROLE [SCOM_HealthService]\nGRANT VIEW ANY DATABASE TO [SCOM_HealthService];\n--GRANT ALTER ANY DATABASE TO [SCOM_HealthService];\nGRANT VIEW ANY DEFINITION TO [SCOM_HealthService];\nGRANT VIEW SERVER STATE TO [SCOM_HealthService];\nGRANT EXECUTE ON sys.xp_readerrorlog TO [SCOM_HealthService];\n \nUSE [msdb];\nGRANT SELECT ON [dbo].[sysjobschedules] TO [SCOM_HealthService];\nGRANT SELECT ON [dbo].[sysschedules] TO [SCOM_HealthService];\nGRANT SELECT ON [dbo].[sysjobs_view] TO [SCOM_HealthService];\nGRANT SELECT ON [dbo].[syscategories] TO [SCOM_HealthService];\nGRANT SELECT ON [dbo].[log_shipping_primary_databases] TO [SCOM_HealthService];\nGRANT SELECT ON [dbo].[log_shipping_secondary_databases] TO [SCOM_HealthService];\nGRANT SELECT ON [dbo].[log_shipping_monitor_history_detail] TO [SCOM_HealthService];\nGRANT SELECT ON [dbo].[log_shipping_monitor_secondary] TO [SCOM_HealthService];\nGRANT SELECT ON [dbo].[log_shipping_monitor_primary] TO [SCOM_HealthService];\nGRANT EXECUTE ON [dbo].[sp_help_job] TO [SCOM_HealthService];\nGRANT EXECUTE ON [dbo].[sp_help_jobactivity] TO [SCOM_HealthService];\nGRANT EXECUTE ON [dbo].[SQLAGENT_SUSER_SNAME] TO [SCOM_HealthService];\nEXEC sp_addrolemember @rolename='PolicyAdministratorRole', @membername='SCOM_HealthService';\nEXEC sp_addrolemember @rolename='SQLAgentReaderRole', @membername='SCOM_HealthService';\n \n \nUSE [master]\nDECLARE @createLoginCommand nvarchar(200)\nSET @createLoginCommand = '\n  CREATE LOGIN '+ QUOTENAME(@accountname) +\n  ' FROM WINDOWS WITH DEFAULT_DATABASE=[master];'\nEXEC(@createLoginCommand);\n-- Add the login to the user-defined server role\nEXEC sp_addsrvrolemember @loginame = @accountname\n  , @rolename = 'SCOM_HealthService'\nDECLARE @createDatabaseUserAndRole nvarchar(max)\nSET @createDatabaseUserAndRole = '';\nSELECT @createDatabaseUserAndRole = @createDatabaseUserAndRole + '\n  USE ' + QUOTENAME(db.name) + ';\n  CREATE USER ' + QUOTENAME(@accountname) +\n  ' FOR LOGIN ' + QUOTENAME(@accountname) + ';\n  CREATE ROLE [SCOM_HealthService];\n  EXEC sp_addrolemember @rolename =\n  ''SCOM_HealthService'', @membername\n  = '+ QUOTENAME(@accountname) + ''\n-- 'ALTER ROLE [SCOM_HealthService] ADD MEMBER '\n  -- '+ QUOTENAME(@accountname) + ';'\nFROM sys.databases db\nLEFT JOIN sys.dm_hadr_availability_replica_states hadrstate ON\n    db.replica_id = hadrstate.replica_id\nWHERE db.database_id  2\n    AND db.user_access = 0\n    AND db.state = 0\n    AND db.is_read_only = 0\n    AND (hadrstate.role = 1 or hadrstate.role is null);\nEXEC(@createDatabaseUserAndRole)\nGO\nGO\n<\/pre>\n
Conclusion<\/h2>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":38,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,99],"tags":[142,2417],"type_dbi":[],"class_list":["post-16854","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-sql-server","tag-microsoft-sql-server","tag-service-sid"],"acf":[],"yoast_head":"\nSQL Server - Granting SQL Server permission to service SID - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/sql-server-granting-sql-server-permission-to-service-sid\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server - Granting SQL Server permission to service SID\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-granting-sql-server-permission-to-service-sid\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-11-22T13:43:57+00:00\" \/>\n<meta name=\"author\" content=\"Christophe Cosme\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Christophe Cosme\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-granting-sql-server-permission-to-service-sid\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-granting-sql-server-permission-to-service-sid\\\/\"},\"author\":{\"name\":\"Christophe Cosme\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/5cbf7c261d165a45977c85f535d6cb6a\"},\"headline\":\"SQL Server – Granting SQL Server permission to service SID\",\"datePublished\":\"2021-11-22T13:43:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-granting-sql-server-permission-to-service-sid\\\/\"},\"wordCount\":348,\"commentCount\":0,\"keywords\":[\"Microsoft SQL Server\",\"Service SID\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-granting-sql-server-permission-to-service-sid\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-granting-sql-server-permission-to-service-sid\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-granting-sql-server-permission-to-service-sid\\\/\",\"name\":\"SQL Server - Granting SQL Server permission to service SID - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2021-11-22T13:43:57+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/5cbf7c261d165a45977c85f535d6cb6a\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-granting-sql-server-permission-to-service-sid\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-granting-sql-server-permission-to-service-sid\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-granting-sql-server-permission-to-service-sid\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server – Granting SQL Server permission to service SID\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/5cbf7c261d165a45977c85f535d6cb6a\",\"name\":\"Christophe Cosme\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g\",\"caption\":\"Christophe Cosme\"},\"description\":\"Christophe Cosme has more than 22 years of experience in architecture, development and implementation of IT solutions including 17 years in the Business Intelligence area as well as 10 years of experience working with the Microsoft SQL Server Business Intelligence suite. His experience and qualifications cover project management, business analysis, functional and technical requirements. His expertise also includes the implementation of IT solutions in national as well as international organizations. Christophe Cosme is Expert in MS SQL Server DB Engine, SSIS, SSAS, SSRS and PowerBI. Prior to joining dbi services, Christophe Cosme was Solution Lead Business Intelligence Diabetes Care at Hoffmann-La-Roche Ltd in Basel . He also worked as Senior Manager Head of Business Intelligence for Swiss International Airlines Ltd. Christophe Cosme holds a Master's degree in Engineering, Automation from the IT\u2013 ENSISA (F). His branch-related experience covers pharmaceuticals, IT and the airline sector.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/christophe-cosme\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server - Granting SQL Server permission to service SID - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/sql-server-granting-sql-server-permission-to-service-sid\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server - Granting SQL Server permission to service SID","og_description":"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 […]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-granting-sql-server-permission-to-service-sid\/","og_site_name":"dbi Blog","article_published_time":"2021-11-22T13:43:57+00:00","author":"Christophe Cosme","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Christophe Cosme","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-granting-sql-server-permission-to-service-sid\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-granting-sql-server-permission-to-service-sid\/"},"author":{"name":"Christophe Cosme","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5cbf7c261d165a45977c85f535d6cb6a"},"headline":"SQL Server – Granting SQL Server permission to service SID","datePublished":"2021-11-22T13:43:57+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-granting-sql-server-permission-to-service-sid\/"},"wordCount":348,"commentCount":0,"keywords":["Microsoft SQL Server","Service SID"],"articleSection":["Database Administration & Monitoring","Database management","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-granting-sql-server-permission-to-service-sid\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-granting-sql-server-permission-to-service-sid\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-granting-sql-server-permission-to-service-sid\/","name":"SQL Server - Granting SQL Server permission to service SID - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-11-22T13:43:57+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5cbf7c261d165a45977c85f535d6cb6a"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-granting-sql-server-permission-to-service-sid\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-granting-sql-server-permission-to-service-sid\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-granting-sql-server-permission-to-service-sid\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server – Granting SQL Server permission to service SID"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5cbf7c261d165a45977c85f535d6cb6a","name":"Christophe Cosme","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g","caption":"Christophe Cosme"},"description":"Christophe Cosme has more than 22 years of experience in architecture, development and implementation of IT solutions including 17 years in the Business Intelligence area as well as 10 years of experience working with the Microsoft SQL Server Business Intelligence suite. His experience and qualifications cover project management, business analysis, functional and technical requirements. His expertise also includes the implementation of IT solutions in national as well as international organizations. Christophe Cosme is Expert in MS SQL Server DB Engine, SSIS, SSAS, SSRS and PowerBI. Prior to joining dbi services, Christophe Cosme was Solution Lead Business Intelligence Diabetes Care at Hoffmann-La-Roche Ltd in Basel . He also worked as Senior Manager Head of Business Intelligence for Swiss International Airlines Ltd. Christophe Cosme holds a Master's degree in Engineering, Automation from the IT\u2013 ENSISA (F). His branch-related experience covers pharmaceuticals, IT and the airline sector.","url":"https:\/\/www.dbi-services.com\/blog\/author\/christophe-cosme\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16854","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/38"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=16854"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16854\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=16854"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=16854"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=16854"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=16854"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}