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.
| Level | Description |
|---|---|
| SQL Instance (DBMS) | Restrictive permissions are applied at this level as SCS will mainly provide the management at DBMS level. |
| Databases | The 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 Role | Permission | State |
|---|---|---|
| customerAdmin | ALTER ANY SERVER AUDIT | DENY |
| customerAdmin | ALTER ANY AVAILABILITY GROUP | DENY |
| customerAdmin | ALTER ANY CREDENTIAL | DENY |
| customerAdmin | ALTER ANY EVENT NOTIFICATION | DENY |
| customerAdmin | ALTER ANY LOGIN | GRANT_WITH_GRANT_OPTION |
| customerAdmin | ALTER ANY LINKED SERVER | GRANT_WITH_GRANT_OPTION |
| customerAdmin | ALTER RESOURCES | DENY |
| customerAdmin | ALTER SERVER STATE | GRANT_WITH_GRANT_OPTION |
| customerAdmin | ALTER SETTINGS | DENY |
| customerAdmin | ALTER TRACE | GRANT_WITH_GRANT_OPTION |
| customerAdmin | AUTHENTICATE SERVER | DENY |
| customerAdmin | CREATE AVAILABILITY GROUP | DENY |
| customerAdmin | CREATE ANY DATABASE | GRANT_WITH_GRANT_OPTION |
| customerAdmin | CREATE DDL EVENT NOTIFICATION | DENY |
| customerAdmin | CREATE ENDPOINT | DENY |
| customerAdmin | CREATE TRACE EVENT NOTIFICATION | DENY |
| customerAdmin | SHUTDOWN | DENY |
| customerAdmin | VIEW ANY DEFINITION | GRANT_WITH_GRANT_OPTION |
| customerAdmin | VIEW ANY DATABASE | GRANT_WITH_GRANT_OPTION |
| customerAdmin | VIEW SERVER STATE | GRANT_WITH_GRANT_OPTION |
| customerAdmin | EXTERNAL ACCESS ASSEMBLY | DENY |
| customerAdmin | UNSAFE ASSEMBLY | DENY |
| customerAdmin | ALTER ANY EVENT SESSION | GRANT |
The following table summarize the most important capabilities of user who are granted membership to the customerAdmin role:
| Action | Remarks |
|---|---|
| Create DB | By statement, SSMS/SSDT or Exec scs_AdminDB.customer.sp_createNewDB |
| Delete DB | By statement or SSMS/SSDT |
| Import DB from bacpac file | SSMS/SSDT |
| Import DB from dacpac file | SSDT |
| Export DB to bacpac files | SSMS/SSDT |
| Export DB to dacpac files | SSMS/SSDT |
| Create login | By statement or SSMS/SSDT |
| Delete login | By statement or SSMS/SSDT |
| Grant membership to bulkadmin and setupadmin roles | Exec scs_AdminDB.customer.sp_grantSrvRole |
| Revoke membership to bulkadmin and setupadmin roles | Exec scs_AdminDB.customer.sp_revokeSrvRole |
| Grant membership to the SQLAgentUserRole | Exec scs_AdminDB.customer.sp_grantAgentJob |
| Revoke membership to the SQLAgentUserRole | Exec scs_AdminDB.customer.sp_revokeAgentJob |
| Set trace flags 1204 and 1222 | Exec scs_AdminDB.customer.sp_setTraceFlag |
| Disable trace flags 1204 and 1222 | Exec scs_AdminDB.customer.sp_disableTraceFlag |
| Get trace flags status | Exec scs_AdminDB.customer.sp_statusTraceFlag |
| Grant the customer admin rights to other new sql logins | Exec scs_AdminDB.customer.sp_createAdmin |
| Grant customer admin rights to new windows login | Exec scs_AdminDB.customer.sp_createWindAuthAdmin |
| Grant customer admin rights to existing sql or windows login | Exec scs_AdminDB.customer.sp_addAdmin |
| Revoke customer admin rights for logins | Exec scs_AdminDB.customer.sp_dropAdmin |
| Grant View Server State permission to a login | Exec scs_AdminDB.customer.sp_grant_ViewSrvState |
| Revoke View Server State permission to a login | Exec scs_AdminDB.customer.sp_revoke_ViewSrvState |
| Read SQL Server Error Log or SQL Agent Error Log | Exec scs_AdminDB.customer.sp_viewSqlLog |
| List DB size | Exec scs_AdminDB.customer.sp_Collect_DB_Space_Stats |
| List DB files details | Select scs_AdminDB.customer.VW_DisplayDbFileInformation |
| List member of server roles | Select scs_AdminDB.customer.VW_DisplayDbRolesMembers |
| List user granular permissions on DB | Select scs_AdminDB.customer.VW_DisplayDbUserPermissions |
| List DB users | Select scs_AdminDB.customer.VW_DisplayDbUsers |
| Create jobs | SSMS or statement |
| Delete jobs | SSMS or statement |
| Modify jobs | SSMS or statement |
| Create Extended Events Session | SSMS or statement |
| Delete Extended Events Session | SSMS 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.
| Name | Configured value | Microsoft usage description | Remarks |
|---|---|---|---|
| Ad Hoc Distributed Queries | 0 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ad-hoc-distributed-queries-server-configuration-option | |
| cost threshold for parallelism | 5 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option | |
| cross db ownership chaining | 0 | https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-cross-database-access-in-sql-server | |
| cursor threshold | -1 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cursor-threshold-server-configuration-option | |
| fill factor (%) | 90 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-fill-factor-server-configuration-option | |
| in-doubt xact resolution | 0 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/in-doubt-xact-resolution-server-configuration-option | |
| max degree of parallelism | 0 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option | Max value configurable 8 |
| max full-text crawl range | 4 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/max-full-text-crawl-range-server-configuration-option | |
| max worker threads | 0 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option | |
| nested triggers | 1 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-nested-triggers-server-configuration-option | |
| network packet size (B) | 4096 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-network-packet-size-server-configuration-option | |
| Ole Automation Procedures | 0 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ole-automation-procedures-server-configuration-option | |
| PH timeout (s) | 60 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ph-timeout-server-configuration-option | |
| remote data archive | 0 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-data-archive-server-configuration-option | |
| transform noise words | 0 | https://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 method | Grantee | Permission set |
|---|---|---|---|
| 1 | SSMS create database statement | Member of customerAdmin | db_owner to executing login |
| 2 | SSMS execute sp_createNewDB | Member of customerAdmin | db_owner to executing login and login dbo_mydbname |
| 3 | Create Database action through vRA/API/Portal | User granted access to vRA/API/Portal | db_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 documentation.