Application and database permissions

Abstract

How to see application and database user permissions used by the Sitecore Installation Framework and the Azure Toolkit.

The Sitecore Installation Framework (SIF) and the Sitecore Azure Toolkit (SAT) set application and database user permissions automatically when you install a role.

Application and worker permissions

To see which permissions are set for a specific application:

  1. Download the XP Scaled packages from the Downloads Portal.

  2. In the root of the .zip, locate the configuration files .zip - for example: XP1 Configuration files 9.1.0 rev. 001564.zip

  3. Open the JSON file for the role that you are interested in - for example, the sitecore-XP1-cd.json file defines permissions for the Content Delivery role. Permissions for worker roles such as the xConnect Search Indexer are bundled with a parent application:

    • xconnect-xp1-collectionsearch.json for the xConnect Search Indexer permissions

    • xconnect-xp1-CortexProcessing.json for the Sitecore Cortex™ Processing Engine permissions

    • xconnect-xp1-MarketingAutomation.json for the Marketing Automation Engine permissions

  4. Search for SetPermissions to see a list of application pool user rights. The following example shows the permissions set for the Content Delivery application pool user:

          "SetPermissions": {
                "Description": "Sets permissions for the app pool user.",
                "Type": "FilePermissions",
                "Params": {
                    "Path" : "[variable('Site.PhysicalPath')]",
                    "Rights": [
                        {
                            "User": "[concat('IIS AppPool\\', parameter('SiteName'))]",
                            "FileSystemRights": "FullControl",
                            "InheritanceFlags": [ "ContainerInherit", "ObjectInherit"]
                        }
                    ]
                }
            },

    For worker roles, search for SetServicePermissions to see a list of service user rights. The following example shows the permissions set for the xConnect Search Indexer service user:

            "SetServicePermissions": {
                "Description": "Set permissions for the service.",
                "Type": "FilePermissions",
                "Params": {
                    "Path" : "[joinpath(variable('Services.IndexWorker.InstallPath'), 'App_Data', 'Logs')]",
                    "Rights": [
                        {
                            "User": "NT AUTHORITY\\LocalService",
                            "FileSystemRights": ["FullControl"],
                            "InheritanceFlags": [ "ContainerInherit", "ObjectInherit"]
                        }
                    ]
                }
            },

    Tip

    Search for SetAppPoolCertStorePermissions (SetServicesCertStorePermissions for worker roles) to see application user access rights to the client certificate.

Database permissions

To see which permissions are set on a specific database:

  1. Download the Web Deploy Package (WDP) for a role that references the database you are interested in from the Downloads Portal. For example, the Content Delivery role references the Core database. WDPs are .zip files that contain all assets required to install a particular role (for example, Sitecore 9.1.0 rev. 001564 (OnPrem)_cd.scwdp.zip is the WDP for the Content Delivery role).

  2. In the root of the .zip, open the .sql script with the name of the database you are interested in. For example, CreateUser.Core.sql contains all permissions for the Core database user:

    Use [PlaceholderForDB]
    Go
    
    alter database [PlaceholderForDB] 
    set containment = partial
    go
    
    CREATE USER [PlaceHolderForUser] WITH PASSWORD = 'PlaceHolderForPassword';
    GO
    
    EXEC sp_addrolemember 'db_datareader', [PlaceHolderForUser];
    EXEC sp_addrolemember 'db_datawriter', [PlaceHolderForUser];
    EXEC sp_addrolemember 'aspnet_Membership_BasicAccess', [PlaceHolderForUser];
    EXEC sp_addrolemember 'aspnet_Membership_FullAccess', [PlaceHolderForUser];
    EXEC sp_addrolemember 'aspnet_Membership_ReportingAccess', [PlaceHolderForUser];
    EXEC sp_addrolemember 'aspnet_Profile_BasicAccess', [PlaceHolderForUser];
    EXEC sp_addrolemember 'aspnet_Profile_FullAccess', [PlaceHolderForUser];
    EXEC sp_addrolemember 'aspnet_Profile_ReportingAccess', [PlaceHolderForUser];
    EXEC sp_addrolemember 'aspnet_Roles_BasicAccess', [PlaceHolderForUser];
    EXEC sp_addrolemember 'aspnet_Roles_FullAccess', [PlaceHolderForUser];
    EXEC sp_addrolemember 'aspnet_Roles_ReportingAccess', [PlaceHolderForUser];
    GO
    
    GRANT EXECUTE TO [PlaceHolderForUser];
    GO