Permissions Model

Permissions at OS level

Permissions at the OS level are reserved for Swisscom. RDP connections are not allowed.

Connections to the SQL Server can be established through SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT) or Visualstudio (VS) from a remote host.

SQL permissions

The aim of the permission model is to allow customers to managed their databases with a high degree of autonomy. The approach to achieve this goal is to separate the DBMS, which is managed by Swisscom, and the DB management as far as possible.

Overview

We differentiate between three levels of permissions. The table summarize the permission model at high level for a first understanding.

LevelDescription
SQL Instance (DBMS)Restrictive permissions are applied at this level as SCS will mainly provide the management at DBMS level.
DatabasesThe customer can create and drop DB's, as well he managed the access to databases assigning permissions according to his business requirements. This means that the customer is responsible for his data and who is going to access it.
Agent (Job Scheduling)Capability to create and manage own jobs is provided.

A full segregation of permission be implemented as the above mentioned three level are interconnected (SQL server security design). The customer should be able to change a defined set of DBMS settings if needed. This is achieved through a predefined set of configurable options made available on the portal and/or through API.

Beside portal/API operations the customer will connect to the DBMS through SSMS and/or SSDT to execute DB and permission actions.

SQL instance permission level

The sysadmin server role is reserved to SCS. The default sa user is disabled. For SCS admin tasks a SQL login named scs_sa is created and member of the sysadmin server role. This user is reserved to SCS.

By a SQL managed service order the customer will be required to define a login name along with a password. This login will be defined as a "customer DBA". A login with enhanced permissions, which will be described below. Basically this user can manage databases, access and own jobs on the SQL instance. Therefore we recommend to assign this role to administrators only.

At instance level the following server-level roles are not available and cannot be assigned:

  • dbcreator
  • diskadmin
  • securityadmin
  • serveradmin
  • sysadmin

These roles are available and can be assigned through the customer DBA to any login.

  • bulkadmin
  • setupadmin

The "customer DBA" capabilities

On every managed instance the server role customerAdmin will be configured. The "customer DBA" mentioned above will be member of the customerAdmin role.

The customer has the rights to add other logins to this server role, i.e. to enroll other users as "customer DBA".

The customerAdmin is member of the followings role

  • Member of the processadmin server role in order to open the activity monitor. This permission enable the "customer DBA" to kill processes.
  • Member of the SQLAgentUserRole and SQLAgentReaderRole in order to create and manage job.

and has been granted with the following instance level rights:

Server RolePermissionState
customerAdminALTER ANY SERVER AUDITDENY
customerAdminALTER ANY AVAILABILITY GROUPDENY
customerAdminALTER ANY CREDENTIALDENY
customerAdminALTER ANY EVENT NOTIFICATIONDENY
customerAdminALTER ANY LOGINGRANT_WITH_GRANT_OPTION
customerAdminALTER ANY LINKED SERVERGRANT_WITH_GRANT_OPTION
customerAdminALTER RESOURCESDENY
customerAdminALTER SERVER STATEGRANT_WITH_GRANT_OPTION
customerAdminALTER SETTINGSDENY
customerAdminALTER TRACEGRANT_WITH_GRANT_OPTION
customerAdminAUTHENTICATE SERVERDENY
customerAdminCREATE AVAILABILITY GROUPDENY
customerAdminCREATE ANY DATABASEGRANT_WITH_GRANT_OPTION
customerAdminCREATE DDL EVENT NOTIFICATIONDENY
customerAdminCREATE ENDPOINTDENY
customerAdminCREATE TRACE EVENT NOTIFICATIONDENY
customerAdminSHUTDOWNDENY
customerAdminVIEW ANY DEFINITIONGRANT_WITH_GRANT_OPTION
customerAdminVIEW ANY DATABASEGRANT_WITH_GRANT_OPTION
customerAdminVIEW SERVER STATEGRANT_WITH_GRANT_OPTION
customerAdminEXTERNAL ACCESS ASSEMBLYDENY
customerAdminUNSAFE ASSEMBLYDENY
customerAdminALTER ANY EVENT SESSIONGRANT

The following table summarize the most important capabilities of user who are granted membership to the customerAdmin role:

ActionRemarks
Create DBBy statement, SSMS/SSDT or Exec scs_AdminDB.customer.sp_createNewDB
Delete DBBy statement or SSMS/SSDT
Import DB from bacpac fileSSMS/SSDT
Import DB from dacpac fileSSDT
Export DB to bacpac filesSSMS/SSDT
Export DB to dacpac filesSSMS/SSDT
Create loginBy statement or SSMS/SSDT
Delete loginBy statement or SSMS/SSDT
Grant membership to bulkadmin and setupadmin rolesExec scs_AdminDB.customer.sp_grantSrvRole
Revoke membership to bulkadmin and setupadmin rolesExec scs_AdminDB.customer.sp_revokeSrvRole
Grant membership to the SQLAgentUserRoleExec scs_AdminDB.customer.sp_grantAgentJob
Revoke membership to the SQLAgentUserRoleExec scs_AdminDB.customer.sp_revokeAgentJob
Set trace flags 1204 and 1222Exec scs_AdminDB.customer.sp_setTraceFlag
Disable trace flags 1204 and 1222Exec scs_AdminDB.customer.sp_disableTraceFlag
Get trace flags statusExec scs_AdminDB.customer.sp_statusTraceFlag
Grant the customer admin rights to other new sql loginsExec scs_AdminDB.customer.sp_createAdmin
Grant customer admin rights to new windows loginExec scs_AdminDB.customer.sp_createWindAuthAdmin
Grant customer admin rights to existing sql or windows loginExec scs_AdminDB.customer.sp_addAdmin
Revoke customer admin rights for loginsExec scs_AdminDB.customer.sp_dropAdmin
Grant View Server State permission to a loginExec scs_AdminDB.customer.sp_grant_ViewSrvState
Revoke View Server State permission to a loginExec scs_AdminDB.customer.sp_revoke_ViewSrvState
Read SQL Server Error Log or SQL Agent Error LogExec scs_AdminDB.customer.sp_viewSqlLog
List DB sizeExec scs_AdminDB.customer.sp_Collect_DB_Space_Stats
List DB files detailsSelect scs_AdminDB.customer.VW_DisplayDbFileInformation
List member of server rolesSelect scs_AdminDB.customer.VW_DisplayDbRolesMembers
List user granular permissions on DBSelect scs_AdminDB.customer.VW_DisplayDbUserPermissions
List DB usersSelect scs_AdminDB.customer.VW_DisplayDbUsers
Create jobsSSMS or statement
Delete jobsSSMS or statement
Modify jobsSSMS or statement
Create Extended Events SessionSSMS or statement
Delete Extended Events SessionSSMS or statement

Server settings configuration

At SQL instance level the customer is not allowed to change directly any settings (i.e. through SSMS).

The options listed below can be configured via Portal. We advise the customers to follow Microsoft recommendations when configuring the setting. The links refer to the latest supported SQL versions; please make sure that the advises apply to older versions as well.

Note: Some changes can heavily affect the server's performance or generate unwanted behaviour.

NameConfigured valueMicrosoft usage descriptionRemarks
Ad Hoc Distributed Queries0https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ad-hoc-distributed-queries-server-configuration-option
cost threshold for parallelism5https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option
cross db ownership chaining0https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-cross-database-access-in-sql-server
cursor threshold-1https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cursor-threshold-server-configuration-option
fill factor (%)90https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-fill-factor-server-configuration-option
in-doubt xact resolution0https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/in-doubt-xact-resolution-server-configuration-option
max degree of parallelism0https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-optionMax value configurable 8
max full-text crawl range4https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/max-full-text-crawl-range-server-configuration-option
max worker threads0https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option
nested triggers1https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-nested-triggers-server-configuration-option
network packet size (B)4096https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-network-packet-size-server-configuration-option
Ole Automation Procedures0https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ole-automation-procedures-server-configuration-option
PH timeout (s)60https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ph-timeout-server-configuration-option
remote data archive0https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-data-archive-server-configuration-option
transform noise words0https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/transform-noise-words-server-configuration-option

SQL agent level permission

The customer DBA is granted membership to the SQLAgentReaderRole group, which enable the user to see all jobs on the server, however to edit the own jobs only (refer to https://technet.microsoft.com/en-us/library/ms188283(v=sql.110).aspx).

Furthermore the customer DBA can assign other logins in the SQLAgentUserRole group.

Please note that the user should be granted access to the database objects used in the jobs.

The following actions are not supported:

  • replication jobs
  • cmd batch (xp_cmdshell)
  • command-line scripts by using ActiveX
  • PowerShell

Manually start, stop, or restart SQL Server Agent can be done through the portal only.

Email notifications through SQL Server Agent are not available.

SQL database level permissions

Permissions at database level can be set through member of the db_owner database role. Membership to the db_owner role is granted at the database creation according to the rules exposed in the table below. Please note: for databases imported through the import functionality see the related chapter.

#DB creation methodGranteePermission set
1SSMS create database statementMember of customerAdmindb_owner to executing login
2SSMS execute sp_createNewDBMember of customerAdmindb_owner to executing login and login dbo_mydbname
3Create Database action through vRA/API/PortalUser granted access to vRA/API/Portaldb_owner to login dbo_mydbname only

Number 1 and 2: the executing members of the customerAdmin role are added automatically as db_owner on each DB created on the instance and they will remain until it is explicitly removed by another member of the db_owner role. If the DB is created through the provided stored procedure an additional user called dbo_dbname will be automatically added as db_owner.

Number 3: one db_owner login will be created. This login can then manage the access permissions to the database.

The scs_sa login will be set as database owner for each DB created on the server and cannot be changed.

For information about the SQL built-in database roles refer please to the Microsoft documentationopen in new window.

This is an information message

Info

Membership to the customerAdmin server role or db_owner gives the capability to backup databases. A backup without the hint "copy only" will break the backup chain and therefore it can affect a restore point in time (if the DB is in full recovery mode).

Last Updated: