Overview
Proper SQL 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 be accessed, what permissions need to be set and how to set these permissions.
What Resources Will Users Need to Access?
The resources that the users will need to access are:
-
The Data Source
-
The Project Repository [the 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 Engines
-
Open Management Studio and log on to the Database Engine using your credentials.
-
From the Object Explorer, expand Security, expand Logins, right-click on the user you want to grant permissions to, and select Properties to open the Login Properties Dialog.
If you do not see the object explorer, navigate to View, and select Object Explorer (or press F8 on your keyboard).
-
The user will need SQL permissions to create SQL databases. So if the user does not currently have these permissions, those permissions will need to be granted using the GRANT CREATE DATABASE TO <user> SQL statement.
-
Navigate to the User Mappings page, select the Databases you wish to grant user permissions on, and check the box for 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 [the database which contains your project(s)]
- The Staging Database
- The Data Warehouse
-
If you are using SQL Server Integration Services (SSIS), you will also need to give the user db_ssisadmin rights on the msdb database.
You can also do this from the Login Properties Dialog. This will allow the user to create and execute Integration Services packages on SQL Server.
-
From the Login Properties Dialog, you will also need to grant the user db_datareader rights on the source database(s).
-
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
-
Open Management Studio and log on to Analysis Services using your credentials.
-
Navigate to the Object Explorer, right-click on your instance, and select Properties.
The Analysis Server Properties Dialog will open.
-
Navigate to the Security tab, and click Add.. to open the Select Users or Groups Dialog. Enter the name of the user you wish to add, click the Check Names button, and then 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.
Comments