[SCSM] Microsoft System Center SQL Server Permissions

After a two-week long time of studying the document and having a lots conversations with the vendor and System admin. I finally figured out the security scope for the SQL DBs on SCSM.

Document download link: http://www.microsoft.com/en-us/download/details.aspx?id=27850

Neither the vendor or the document would cover 100% correct settings on practical environment. At least not mine in my company.

I will list the summary below


Usually Microsoft Service Manager will create a database and 5 other Data warehouse DBs

• ServiceManager: Configuration Items, Work Items,Incidents
• DWStagingAndConfig: make up the data warehouse. It is where we store all of our management packs, ETL (extract, transform, load) configuration, and other configuration information. It is also the initial store for the source data coming from the Service Manager CMDB. Then transformed into DWRepository DB.
• DWRepository: make up the data warehouse. It  is where we transform the extracted source data into the reporting optimized structure. Through the load process, the content for DWDataMart DB
• DWDataMart: make up the data warehouse. It is the database for our published data that gets consumed by the reports.
• OMDWDataMart: for collecting data from Operations Manager and Configuration Manager respectively.
• CMDWDataMart: for collecting data from Operations Manager and Configuration Manager respectively.
• ReportServer(If you applied it as default name): SSRS Reporting Database
• ReportServerTempDB: SSRS Reporting Database

For my environment, we used "Four-computer topology" (figure from the document)

So we separated ServiceManager from DW due to the performance concern below:

• ServiceManager: DB will be hit every minute
• DWStagingAndConfig: DB will be hit every 5 minute
• DWRepository: DB will be hit every 30 minute
• DWDataMart: Size keeps growing, need a disk space consideration
• OMDWDataMart and CMDWDataMart: small databases and not too busy

We have two accounts: one for ServiceManager and Datawarehouse service account (SCSM). The other for Reporting account (SCSMREP. not: it's NOT the SSRS service account run by Windows, we have another service account for SSRS Service)

Below is the security scope.

****DO NOT DROP Public role****

**ServiceManager service account: SCSM (Server 1)

• Local Admin in Windows Server.

• SQL Server level role: SA, [serveradmin], [setupadmin] (These 3 fixed server roles will assign during installation, you can drop after installation). [Public], [SecurityAdminn]

• DB level role:

ServiceManager - configsvc_users, db_accessadmin, db_datareader,db_datawriter
,db_ddladmin, db_securityadmin, dbmodule_users,sdk_users,sql_dependency_subscriber
dwsynch_users, Mpsync_users (These two were not addressed in the document but practically existed)
db_owner (If you drop SA, [serveradmin], [setupadmin], then it MIGHT need, but I don't have time to test, so just add it. Per Microsoft PFE, the SCSM account should be db_owner for all the DBs. But actually, this one didn't assign with)

**Datawarehouse service account: SCSM (Server 2)

• Local Admin in Windows Server.

• SQL Server level role: SA, [serveradmin], [setupadmin] (These 3 fixed server roles will assign during installation, you can drop after installation). [Public], [SecurityAdminn]

• DB level role:

DWStagingAndConfig: configsvc_users , db_accessadmin, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, dbmodule_users, sdk_users, sql_dependency_subscriber, db_owner
DWRepository: db_owner
DWDataMart: db_owner
CMDWDataMart: db_owner (not addressed in Microsoft WhitePaper)
Content Managers in SSRS
SSAS Admins


**Reporting account: SCSMREP (Server 2)

• Local Admin in Windows Server.

• SQL Server level role: Doesn't need to assign any server roles except Public.

• DB level role:

DWStagingAndConfig: db_datareader
DWRepository: db_datareader, reportuser (These 2 roles are mentioned in the document, but actually  no such DB roles are checked in the DB, and no reportuser role. So I manully added [db_datareader] and [db_owner] to it)
DWDataMart:     db_datareader, reportuser
OMDWDataMart: db_datareader, reportuser
CMDWDataMart: db_datareader, reportuser
Browser in SSRS
SSAS admins