SQL Server Permissions Overview

Overview

The following information will help to configure the minimum SQL Server security settings required for your HelpMaster 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. 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 an 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.

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) and the database server (where SQL Server is installed). HelpMaster supports both of the following options:

Trusted Windows account

When the trusted Windows account authentication is used, HelpMaster will attempt to connect to the Microsoft SQL Server HelpMaster database via the Windows account that is currently logged into the machine that HelpMaster 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 will attempt to connect to the Microsoft SQL Server HelpMaster 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

Different functions within HelpMaster require different database privileges. For the most part, each module of the HelpMaster 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 user. It is possible to create just one account to cater for both types of logins, however this means that non-HelpMaster administrators will be connecting to SQL Server with an account that has elevated database privileges that they may never use.

Configuring appropriate Security access

The following SQL Server scripts will create SQL Server logins for both Administrators and non-administrators of HelpMaster. Please run the appropriate script(s) for your chosen authentication method.

If using Windows authentication for connecting to SQL Server: Windows Authentication script

If using SQL Server authentication for connecting to SQL Server: SQL Server Authentication script

For further details about this, please refer to your database administrator.

See Also

Network security configuration

SQL Server Authentication options (external link)