Hello helpdeskers,
Recently we've been getting a few questions regarding the minimum database permissions that are required for using HelpMaster Pro. Below is an updated page taken from the helpfile that explains the best way to achieve this.
Hope this helps
Rod
Security Settings for the HelpMaster Pro database
Overview
The following information will help to configure the minimum SQL Server security settings required for your HelpMaster Pro database. Please note that the information here relates to database security, that is, the security configuration of Microsoft SQL Server - not the security model within the HelpMaster Pro. Note also that the information offered here is just one of many different ways of configuring SQL Server security. Database security is a complex subject, and to implement a SQL Server security strategy that best suits your environment, it is highly recommended that you consult with your database administrator before and during the setup and configuration of HelpMaster Pro.
SQL Server Connection methods
When connecting to any Microsoft SQL Server database, there are 2 options for establishing the network connection between the client computer (the computer running HelpMaster Pro) and the database server(where SQL Server is installed). HelpMaster Pro supports both of the following options:
- Trusted Windows account.
When the trusted Windows account authentication is used, HelpMaster Pro will attempt to connect to the Microsoft SQL Server HelpMaster Pro database via the Windows account that is currently logged into the machine that HelpMaster Pro is being used on. In order for this to be successful, the Windows account must be granted permission to SQL Server first.
- A specific SQL Server account and password.
When a specific SQL Server Account and password is used, HelpMaster Pro will attempt to connect to the Microsoft SQL Server HelpMaster Pro database via a specific account that has already been configured on the Microsoft SQL Server. In order for this to be successful, the SQL Server account must be created within SQL Server first.
For complete information about the different types of connection methods, please refer to the Microsoft SQL Server documentation.
Administrator vs. Non administrator within HelpMaster Pro
Different functions within HelpMaster Pro require different database privileges. For the most part, each module of the HelpMaster Pro suite will require database privileges that include the standard database operations of reading, writing and deleting data within the tables of the database. Execute permissions on stored procedures is also required. However, certain administrative functions such as the creating or editing of custom fields will require the removal (drop view) and creation (create view) of database views. These database operations (drop view and create view) require a higher level of database access than the more standard operations of reading and writing of data. For this reason, it is recommended that 2 different types of SQL Server database logins are created to cater for each type of HelpMaster Pro user. It is possible to create just one account to cater for both types of logins; however this means that non-HelpMaster Pro administrators will be connecting to SQL Server with an account that has elevated database privileges that they will never use.
Configuring appropriate Security access
The following SQL Server 2005 scripts will create SQL Server logins for both Administrators and non-administrators of HelpMaster Pro. These accounts are SQL Server accounts, not Windows accounts. If you wish to use Windows authentication, you will need to configure these, and then apply the same security configuration as outlined in the scripts. For further details about this, please refer to your database administrator.
Administrator
The following script will create a new SQL Server login the minimum database permissions required to use all features of HelpMaster Pro. This style of login should be used for an administrator of the system.
-- Insert the name of your HelpMaster Pro database in the line below
USE [HelpMaster Pro database name goes here]
GO
-- Create a new SQL Server login
CREATE LOGIN [HelpMaster Pro Administrator] WITH PASSWORD=N'Password goes here', DEFAULT_DATABASE=[HelpMaster Pro database name goes here], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
-- Now associate this new SQL Server login with a user in the HelpMaster Pro database
-- Also associate this database user with the "dbo" schema
-- The "dbo" schema contains all of the HelpMaster Pro database objects (tables, views, stored procedures etc)
CREATE USER [HelpMaster Pro Administrator] FOR LOGIN [HelpMaster Pro Administrator] WITH DEFAULT_SCHEMA=[dbo]
GO
-- Now grant the ability to "execute" the stored procedures contained within the "dbo" schema
GRANT EXECUTE ON SCHEMA::[dbo] TO [HelpMaster Pro Administrator]
GO
-- The build-in SQL Server role "db_datareader" allows logins to read data
EXEC sp_addrolemember N'db_datareader', N'HelpMaster Pro Administrator'
GO
-- The build-in SQL Server role "db_datawriter" allows logins to write data
EXEC sp_addrolemember N'db_datawriter', N'HelpMaster Pro Administrator'
GO
-- Only administrators will have this privilege.
-- This permission is required for re-creating the custom field crosstab views used by HelpMaster Pro
-- which is triggered by either updating or creating a new custom field from the System Codes screen,
-- or by updating the custom field definition of an entity via the "Administration > Custom fields" menu with HelpMaster Pro
GRANT CREATE VIEW TO [HelpMaster Pro Administrator]
Non-administrator
The following script will create a new SQL Server login the minimum database permissions required to use all features of HelpMaster Pro, except the administrative features of creating new custom system codes. This style of login should be used for all non-administrative users of HelpMaster Pro.
-- Insert the name of your HelpMaster Pro database in the line below
USE [HelpMaster Pro database name goes here]
GO
-- Create a new SQL Server login
CREATE LOGIN [HelpMaster Pro Administrator] WITH PASSWORD=N'Password goes here', DEFAULT_DATABASE=[HelpMaster Pro database name goes here], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
-- Now associate this new SQL Server login with a user in the HelpMaster Pro database
-- Also associate this database user with the "dbo" schema
-- The "dbo" schema contains all of the HelpMaster Pro database objects (tables, views, stored procedures etc)
CREATE USER [HelpMaster Pro Administrator] FOR LOGIN [HelpMaster Pro Administrator] WITH DEFAULT_SCHEMA=[dbo]
GO
-- Now grant the ability to "execute" the stored procedures contained within the "dbo" schema
GRANT EXECUTE ON SCHEMA::[dbo] TO [HelpMaster Pro Administrator]
GO
-- The build-in SQL Server role "db_datareader" allows logins to read data
EXEC sp_addrolemember N'db_datareader', N'HelpMaster Pro Administrator'
GO
-- The build-in SQL Server role "db_datawriter" allows logins to write data
EXEC sp_addrolemember N'db_datawriter', N'HelpMaster Pro Administrator'
GO