Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet 365 Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Feedback

How to Set Table Level Security for Users on the Data Warehouse (Jet Enterprise 2014 and Earlier)


Overview

When end users are creating reports based off of the data warehouse database it may be necessary to restrict users to only have access to certain tables or views in this database. This article describes how to set up table level security for users to access the data in the data warehouse through SQL Management Studio.

This article addresses adding security to a SQL database using SQL 2012. These steps may vary slightly dependent upon the version of SQL that you are using.

Process

Creating Database Roles

The user should first open SQL Management Studio, log in to the Database Engine for the desired server, and navigate to the data warehouse database. Security will be configured under the Security -> Roles node for the SQL database. The user should right-click the Roles node and select New -> New Database Role.


The user should assign the role a descriptive Role Name that clearly identifies the security rights that this role grants. For example, the role that will be added in this article will allow users to access the sales transactions tables so we will name the role Sales Transactions Role.

Once the role has been name the user should navigate to the Securables tab on the left. The user can click the Search button and then select "All objects of the types..." to get a screen where the desired object types can be selected.


Tables and Views are the two most common objects that users are granted access to.


A list of all tables and views in the database will be returned.

It is important to note that the only tables and views that end users should be able to query are those that are appended with a "_V" after the object name. This represents the Valid table. Users should not be granted access to the _L (Log), _M (Message), or _R (Raw) tables.
To grant a user permissions to query data from a particular table, the table should be select and the check box on the Select row for the Grant column should be checked in the pane below the object window. In the screenshot below the role has been granted access to the Posted Sales Transactions_V table which will allow users of this role to query the table.


Continue this process until all desired tables and views have been assigned. Once this is finished click the Ok button to save the new database role.

Assigning Roles to Users

After the database role has been set up, the role must then be assigned to a user. The user must already exist on the SQL server in order to assign a role. Adding new users to SQL Server is outside of the scope of this document.

Right-click the desired user and select Properties. This will open the Login Properties window. The user should then select the "User Mapping" page on the left.


As illustrated above, the check box to the left of the data warehouse database should be checked. A list of database roles appears below the list of databases. The user should check the box next to all roles that are to be assigned to that particular user.

It is also possible to assign roles to entire Active Directory groups instead of individual users. This can make sure process of maintaining security much easier.
Once all of the desired roles have been selected the user can click Ok to save the login properties for the user. The user should now be able to access the tables in the data warehouse that were defined in the database role.

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

Comments