Knowledge Base Article: SQL Server Permissions for HelpMaster

SQL Server Permissions for HelpMaster

INFO: Minimum SQL Server database permissions

security settings minimum permissions sql server 2005 2008 2012 helpmaster pro database windows authentication sql upgrade permission

HelpMaster > Database > Permissions

Database

10/9/2008 10:52:35 AM

8/22/2017 2:57:48 PM

Average Rating (from {{model.ratings}} ratings)
0 Attachments

Applies to

  • HelpMaster v8 and above

  • Microsoft SQL Server 2008 or higher

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 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 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.

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:

  1. 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.

  2. A specific SQL Server Login 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 HelpMaster non-administrators will be connecting to SQL Server with an account that has elevated database privileges that they may never use.

Configuring appropriate Security access

Manually Within SQL Server Management Studio

The following minimum permissions for users / user groups at the 'Users' object level are required;

  1. Expand the "Security" then "Logins" tree from the root of the Object Explorer not from the HelpMaster database, right mouse click on your user, or create/add a new user, and then select the "Properties" option.

  2. From the Database Properties screen select the "User Mapping" page, highlight your HelpMaster database and make sure that the "Map" check box is ticked.

  3. In the "Database role membership:" box select the 'db_datareader', 'db_datawriter', and 'public' roles. These 3 roles are required for ALL HelpMaster users regardless of their HelpMaster roles.

  4. Now expand the HelpMaster database tree, then the "Security" and "Users" tree below it.

  5. Right mouse click on the user you created or added above, and then select the "Properties" option.

  6. Highlight the "Securables" page on the left pane and then click on the "Add..." or "Search..." button under "Securables:" in the right pane.

  7. From the "Add Objects" pop-up, select "Specific objects..." and click OK.

  8. From the "Select Objects" screen click on the "Object Types" button, scroll down the list and select "Schemas" then click "OK".

  9. In the "Enter the object names to select" box type in "[dbo]" then click "OK".

  10. Now back on the "Database User" screen, from the "Explicit permissions for dbo:" pane, select the following permission(s) by selecting the "Grant" column check box based on the user / user group's HelpMaster role;

    Non-Admin User: "Execute" permission only.
    Admin User: "Execute" & "Alter" permissions.

  11. Click OK to save the users object level permissions changes.

The following minimum permissions for users / user groups at the 'Database' object level are required;

  1. Right mouse click on your HelpMaster database and select the "Properties" option.

  2. From the Database Properties screen select the "Permissions" page and highlight the user name you wish to modify or "Add..." the user then highlight it. NOTE: If the user already has elevated role based permissions then the user won't be available for selection from this screen and the following steps will not be required.

  3. Now from the "Explicit permissions for [User]" pane, scroll down and select the following permission(s) by selecting the "Grant" column check boxes based on the user / Group's HelpMaster role;

    Non-Admin User: "Connect" permission only.
    Admin User: "Connect", "Create view", & "View database state" permissions.

  4. Click OK to save the database object level permissions changes.

These are the absolute minimum permissions requirements for users of the HelpMaster Desktop application based on HelpMaster roles. Errors may occur if this minimum set of permissions are not implemented.

 

Using SQL Scripts to grant SQL permissions to Windows domain login accounts or groups

Non-administrator

The following script will create a new Windows authenticated login with the minimum database permissions required to use all features of HelpMaster, except the administrative features of creating new custom fields and other system codes. This style of login should be used for all non-administrative users of HelpMaster. Don't forget to replace all occurrences of [HelpMaster Database Name Goes Here] and [DOMAIN\UserName_or_Group] with the actual names before running the script.

USE [master] -- Server level execution

GO

-- Add an AD user account to SQL server logins

CREATE LOGIN [DOMAIN\UserName_or_Group] FROM WINDOWS WITH DEFAULT_DATABASE=[HelpMaster Database Name Goes Here], DEFAULT_LANGUAGE=[us_english]

GO

-- Now associate this new SQL Server login with a user in the HelpMaster database

-- Also associate this database user with the "dbo" schema. The "dbo" schema contains all of the HelpMaster database objects (tables, views, stored procedures etc)

-- Insert the name of your HelpMaster database in the line below

USE [HelpMaster Database Name Goes Here] -- Database level execution

CREATE USER [DOMAIN\UserName_or_Group] FOR LOGIN [DOMAIN\UserName_or_Group] WITH DEFAULT_SCHEMA=[dbo]

GO

-- Grant the build-in SQL Server role "db_datareader" allows logins to read data

EXEC sp_addrolemember N'db_datareader', N'DOMAIN\UserName_or_Group'

GO

-- Grant the build-in SQL Server role "db_datawriter" allows logins to write data

EXEC sp_addrolemember N'db_datawriter', N'DOMAIN\UserName_or_Group'

GO

-- Grant the ability to "execute" the stored procedures contained within the "dbo" schema

GRANT EXECUTE ON SCHEMA::[dbo] TO [DOMAIN\UserName_or_Group]

GO


Administrator

The following script will create a new Windows authenticated login with the minimum database permissions required to use all features of HelpMaster. This style of login should be used for an administrator of the system. Copy everything below and paste it into a new query window of Microsoft SQL Server Management Studio. Don't forget to replace all occurrences of [HelpMaster Database Name Goes Here] and [DOMAIN\UserName_or_Group] with the actual names before running the script.

USE [master] -- Server level execution

GO

-- Add an AD user account to SQL server logins

CREATE LOGIN [DOMAIN\UserName_or_Group] FROM WINDOWS WITH DEFAULT_DATABASE=[HelpMaster Database Name Goes Here], DEFAULT_LANGUAGE=[us_english]

GO

-- Now associate this new SQL Server login with a user in the HelpMaster database

-- Also associate this database user with the "dbo" schema. The "dbo" schema contains all of the HelpMaster database objects (tables, views, stored procedures etc)

-- Insert the name of your HelpMaster database in the line below

USE [HelpMaster Database Name Goes Here] -- Database level execution

CREATE USER [DOMAIN\UserName_or_Group] FOR LOGIN [DOMAIN\UserName_or_Group] WITH DEFAULT_SCHEMA=[dbo]

GO

-- Grant the build-in SQL Server role "db_datareader" allows logins to read data

EXEC sp_addrolemember N'db_datareader', N'DOMAIN\UserName_or_Group'

GO

-- Grant the build-in SQL Server role "db_datawriter" allows logins to write data

EXEC sp_addrolemember N'db_datawriter', N'DOMAIN\UserName_or_Group'

GO

-- Grant the build-in SQL Server role "db_owner" allows logins to administer the database

EXEC sp_addrolemember N'db_owner',N'DOMAIN\UserName_or_Group'

GO

-- Grant the ability to "execute" the stored procedures contained within the "dbo" schema

GRANT EXECUTE ON SCHEMA::[dbo] TO [DOMAIN\UserName_or_Group]

GO

-- Grant the ability to "alter" the "dbo" schema which contains the views that need to be re-created

GRANT ALTER ON SCHEMA::[dbo] TO [DOMAIN\UserName_or_Group]

GO

GRANT CREATE TABLE TO [DOMAIN\UserName_or_Group] -- Used to create tables during database upgrades

GO

GRANT CREATE PROCEDURE TO [DOMAIN\UserName_or_Group] -- Used to create stored procedures during database upgrades

GO

GRANT CREATE VIEW TO [DOMAIN\UserName_or_Group] -- Used to create views during database upgrades as well as updating system codes

GO 

GRANT VIEW DATABASE STATE

TO [DOMAIN\UserName_or_Group]

GO

Use [Master] -- Required for the "Alter settings" permission. This is a server-level permission

GRANT ALTER SETTINGS TO [DOMAIN\UserName_or_Group] -- Required for a database reconfigure statement used for Full Text indexing operations

 

Using SQL Scripts to create corresponding SQL login accounts

The following SQL Server 2005 scripts will create SQL Server logins for both Administrators and non-administrators of HelpMaster. 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 above. For further details about this, please refer to your database administrator.

Non-administrator

The following script will create a new SQL Server login the minimum database permissions required to use all features of HelpMaster, except the administrative features of creating new custom system codes. This style of login should be used for all non-administrative users of HelpMaster.

-- Insert the name of your HelpMaster database in the line below

USE [HelpMaster database name goes here]

GO

-- Create a new SQL Server login

CREATE LOGIN [HelpMaster User] WITH PASSWORD=N'Password goes here', DEFAULT_DATABASE=[HelpMaster 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 database

-- Also associate this database user with the "dbo" schema

-- The "dbo" schema contains all of the HelpMaster database objects (tables, views, stored procedures etc)

CREATE USER [HelpMaster User] FOR LOGIN [HelpMaster User] 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 User]

GO

-- The built-in SQL Server role "db_datareader" allows logins to read data

EXEC sp_addrolemember N'db_datareader', N'HelpMaster User'

GO

-- The built-in SQL Server role "db_datawriter" allows logins to write data

EXEC sp_addrolemember N'db_datawriter', N'HelpMaster User'

GO  

Administrator

The following script will create a new SQL Server login the minimum database permissions required to use all features of HelpMaster. This style of login should be used for an administrator of the system.

-- Insert the name of your HelpMaster database in the line below

USE [HelpMaster Database Name Goes Here]

GO

-- Create a new SQL Server login

CREATE LOGIN [HelpMaster Administrator] WITH PASSWORD=N'Password goes here', DEFAULT_DATABASE=[HelpMaster Sample] , DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

-- Now associate this new SQL Server login with a user in the HelpMaster database

-- Also associate this database user with the "dbo" schema

-- The "dbo" schema contains all of the HelpMaster database objects (tables, views, stored procedures etc)

CREATE USER [HelpMaster Administrator] FOR LOGIN [HelpMaster Administrator] WITH DEFAULT_SCHEMA=[dbo]

GO

-- Grant the build-in SQL Server role "db_datareader" allows logins to read data

EXEC sp_addrolemember N'db_datareader', N'HelpMaster Administrator'

GO

-- Grant the build-in SQL Server role "db_datawriter" allows logins to write data

EXEC sp_addrolemember N'db_datawriter', N'HelpMaster Administrator'

GO

-- Only administrators will have the following permissions

-- Grant the build-in SQL Server role "db_owner" allows logins to administer the database

EXEC sp_addrolemember N'db_owner', N'HelpMaster Administrator'

GO

-- Grant the ability to "execute" the stored procedures contained within the "dbo" schema

GRANT EXECUTE ON SCHEMA::[dbo] TO [HelpMaster Administrator]

GO

-- Grant the ability to "alter" the "dbo" schema which contains the views that need to be re-created

GRANT ALTER ON SCHEMA::[dbo] TO [HelpMaster Administrator]

GO

GRANT CREATE TABLE TO [HelpMaster Administrator] -- Used to create tables during database upgrades

GO

GRANT CREATE

PROCEDURE TO [HelpMaster Administrator]

-- Used to create stored procedures during database upgrades

GO

GRANT CREATE VIEW TO [HelpMaster Administrator] -- Used to create views during database upgrades as well as updating system codes

GO

GRANT VIEW DATABASE STATE TO [HelpMaster Administrator]

GO

Use [Master] -- Required for the "Alter settings" permission. This is a server-level permission

GRANT ALTER SETTINGS TO

[HelpMaster Administrator] -- Required for a database reconfigure statement used for Full Text indexing operations