Exact Globe+

How to setup security in SQL when working with Exact Globe+

Issue:

I want to tighten my security in SQL for the users of Exact Globe+, but how should I set this up?

Solution:

Security in SQL can be very easy and straight forward, but it can be very complex as well. This document provides a guideline to setup the security in SQL for Exact Globe+ in a manageable way and to setup the security that users can work with in Exact Globe+. In this document it is assumed that an active directory (domain) is installed, if this is not the case this needs to be done first otherwise working with Exact Globe+ in a multi-user environment is not supported.

Note: If you are working with Exact Globe+ with extended functionality, it is highly recommended to use Mixed Mode (SQL Server authentication and Windows authentication) option to access SQL Server. 

When setting up security it is always advisable to do this through groups. This ensures security can be easily distributed by putting people into groups and by moving them out of the groups. Take note of the following:

1. To make backups (Exact backup, not a SQL backup) from Exact Globe+, you need special rights in SQL;
2. To restore backups (Exact backup, not a SQL backup) from Exact Globe+, you need special rights in SQL;
3. To create a new company in Exact Globe+, you need special rights in SQL;
4. To delete a company in Exact Globe+, you need special rights in SQL.

Setup access for Exact Globe+ users in SQL:
Once you have completed the steps below, you will not be able to do any of the four tasks above.

• Setup a group for the Exact users in the active directory and put the users that have to work with Exact Globe+ in this group;
• Give this group rights in SQL Server through the enterprise manager or SQL Server Management Studio.
  • Go to Start > Programs > Microsoft SQL Server > Enterprise Manager or Start > Programs > Microsoft SQL Server > SQL Server Management Studio.
  • Go to the SQL Server that you want to manage and open the container Security to login.
  • Right-click in the right hand pane and create a new login.
  • Add the group you created in the active directory.
  • Go to the tab database access and mark the databases where you want the users to have access. Click on OK.
  • Go to the container Databases and go to the databases where the users have access. Remove the database user guest.

After these steps the users have the ability to connect to Exact Globe+ and open a database, but they cannot run any queries through other programs (Excel, Query analyzer, Enterprise Manager, etc) other than Exact Globe+.

To be able to do the special actions, you need special access rights in SQL. Below is a table with the functions in Exact Globe+ and the role required in SQL.

Setup special access for Exact Globe+ users in SQL:

• Setup a group for the Exact users in the active directory and put the users that have to work with Exact Globe+ in this group;
• Give this group rights in SQL Server through the enterprise manager or SQL Server Management Studio.
  • Go to Start > Programs > Microsoft SQL Server > Enterprise Manager or Start > Programs > Microsoft SQL Server > SQL Server Management Studio.
  • Go to the SQL Server you want to manage and open the container Security, and then to login.
  • Right-click in the right hand pane and create a new login.
  • Add the group you created in the active directory.
  • Go to the tab server role and put the users in the role they need in order to do what they have to do (see table above). Click OK.