Jet Reports Homepage |  Community Forum |  Downloads |  Submit A Ticket |  Jet Express Support
Feedback

Jet Data Manager Minimum SQL Server Permissions


Overview

Proper permissions must be configured in order to allow users to work with the Jet Data Manager. During the course of this article we will discuss the resources that need to accessed, what permissions need to be set and how to set these permissions.

Setting Database Permissions for Users

Setting Permissions on Analysis Services

What Resources Will Users Need to Access?

The resources that the users will need to access are:

  • The Data Source
  • The Project Repository (Database that contains your project(s))
  • The Staging Database
  • The Data Warehouse
  • The MSDB System Database (Only if using SQL Server Integration Services (SSIS))
  • Analysis Services

These permissions only need to be set for users who work directly with the Jet Data Manager. Users that only need to access the cubes for reporting proposes have their permissions defined within the Jet Data Manager itself. See Configuring OLAP Security for instructions.

Setting Database Permissions for Users

Granting users the proper roles will allow them to access the resources they require, but in order to set roles for a user you will need SysAdmin rights on the SQL Server.

You will need SysAdmin rights to make the following changes.

Setting Permissions on the Database Engine

1. Open Management Studio and log on to the Database Engine using your credentials.


2. From the Object Explorer expand Security → expand Logins → Right click the user you want to grant permissions to → Click Properties to open the Login Properties Dialog.


If you do not see the object explorer, navigate to view → Object Explorer, or press F8 on your keyboard.

3. The user will need SQL permissions to create SQL databases, so if the user does not currently have these permissions they will need to be granted using the GRANT CREATE DATABASE TO <user> SQL statement.

4. Navigate to the User Mappings page → Select the Databases you wish to grant user permissions on → Check db_owner.

In this example we are granting db_owner to the SWPROS\DDL user on the Data Warehouse and on the Staging Database . Continue to enable db_owner on all required Databases:

  • The Project Repository (Database that contains your projects)
  • The Staging Database
  • The Data Warehouse


4. From the Login Properties Dialog you will also need to grant the user db_ssisadmin rights on the msdb database. ( Only if using SQL Server Integration Services (SSIS)). This will allow the user to create and execute Integration Services packages on SQL Server.


5. From the Login Properties Dialog you will also need to grant the user db_datareader rights on the source database(s).


5. Once all permissions have been set click OK to save your changes.

You can also define User Groups to allow access to resources. This allows the administrator to assign roles to a group of users as opposed to changing each user's individual settings . The user will inherit the roles assigned to the group which provides for easier system administration. Setting up User Groups is outside of the scope of this article.

Setting Permissions on Analysis Services

1. Open Management Studio and log on to Analysis Services using your credentials.

2. Navigate to the Object Explorer → Right click on your instance → Click Properties → The Analysis Server Properties Dialog opens

3. Navigate to the Security tab → Click Add.. to open the Select Users or Groups Dialog Enter the name of the user you wish to add click Check Names → Click OK to add the user.

The user account that is set to start the SQL Analysis Services service on the machine will need to have db_datareader permissions set on the data warehouse database as well. This will ensure that when the cubes are processed that the data can be read from the data warehouse database.



Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments