This chapter assumes that "Siemens GMS HDB Service" runs or used to run under a specific user (e.g. HdbServiceUser) and you need to remove the advanced privileges from this account.
The maintenance stored procedures are responsible for many features. Some of them, not all, require advanced privileges but the procedures are designed not to fail if these permissions are missing and would require manual administration for the features to function as expected.
Step 5.1: Check existing privileges
Execute the following query to identify logins with advanced privileges and confirm that the account name of the HDB service is listed:
SELECT name, permission_name FROM sys.server_permissions a inner join sys.server_principals b on a.grantee_principal_id=b.principal_id WHERE permission_name IN ('VIEW SERVER STATE','CREATE ANY DATABASE','CREATE DATABASE');
Step 5.2: Remove advanced privileges
Execute following query in the database System Databases/master:
REVOKE VIEW SERVER STATE FROM [<Hdb-Service-Account-Name>]
REVOKE CREATE ANY DATABASE FROM [<Hdb-Service-Account-Name>]
Beware that you also need to add the domain of the account name (e.g. AD001\HdbServiceUser).
Please note that the CREATE ANY DATABASE privilege should not be revoked if the archives need to be mounted from SMC.
Desigo CC MP 2.1
In MP 2.1 the privilege "CREATE DATABASE" was granted, which is not the same as the current "CREATE ANY DATABASE". If you need to be entirely sure, execute the following statement as well:
REVOKE CREATE DATABASE FROM [<Hdb-Service-Account-Name>]
Step 5.3: Recheck privileges
Re-execute the query from Step 1 and confirm that the HDB service user account (e.g. HdbServiceUser) is not listed anymore.
Step 5.4: Alternative for removed "VIEW SERVER STATE" permission
This step is not required for databases running in recovery model "Simple" and is only required for databases running in recovery model "Full".
VIEW SERVER STATE" permission was required to observe the fill level of the HDB transaction log and in turn to start the process which recycles the log space.
If the fill level cannot be observed, for example due to removed permissions, HDB works fine but, the log will not be recycled, which has to be done separately. If the log is not recycled the HDB would be unavailable after some time leading to loss of data and may even go to an unrecoverable state.
Start SQL MS and log in as a member of the sysadmin fixed server role.
- Create a SQL Server Agent job called "pctLogUsedJob"
- Add one step (e.g. Step 1) to the job executing:
UPDATE Globals SET GlobalInt=101 WHERE GlobalName = 'pctLogUsedCur';
- You might want to run this job as "HdbServiceUser", like in "invLoopJob" by selecting the appropriate "Run as user" in "Advanced" page.
- Create an SQL SA alert of type "SQL Server performance condition alert", in object select "Databases" and in counter select "Percent Log Used” and in instance select the HDB, raising if the counter rises above 50. In page "Response" activate the flag "Execute job" and select the job "pctLogUsedJob" created in the above step. Though it is not mandatory for the functionality, in page "Options" you may also want to increase the "Delay between responses".
Step 5.5: Alternative for removed "CREATE ANY DATABASE" permission
This step is only required if the HDB have Long Term Storage activated.
The "CREATE ANY DATABASE" permission is required to create and mount archive databases. The following alternative is to create archive databases without needing CREATE ANY DATABASE privilege. There is no alternative for mount archive databases and needs the CREATE ANY DATABASE privilege for the archives to be mounted from SMC.
- In SMC change the HDB Administration model from Automatic to Manual. This will skip the “CREATE ANY DATABASE” command when creating the archive pieces. Instead, HDB will expect a database to exist with the name it would use for the archive pieces creation (e.g. HDB_S001_000001 under C:\GMSDatabases).
- Create a stack of databases, following the HDB naming convention for archive pieces. Create as many and as small as possible (HDB will increase the size in the right moment) and with FILEGROWTH = 1024KB for data file and FILEGROWTH = 10%, MAXSIZE = UNLIMITED for log file. The "HdbServiceUser" should be a member of db_owner but may not be the dbo itself. Just create empty archive databases, the maintenance tasks running within HDB will take the database and create the structure (tables) within the database.
HDB maintenance stored procedures will collect the databases one by one from the stack. So, make sure that the stack never gets empty or else there is a possibility of data loss. The HDB naming convention for the archive pieces is "<NameOfHdb>_S<xxx>_<yyyyyy>" where "x" is the number of the storage and "y" is the sequence. If you plan, for example, to activate storage 17 and expect one archive piece to last for about one quarter, than you should create four databases (e.g. HDB_S017_000001, HDB_S017_000002, HDB_S017_000003 and HDB_S017_000004) for the next year.