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