To manage  the permissions in databases, the system allows the usage of multiple user-defined roles. Domain users and group accounts can be assigned as members of these roles to assign permissions for database.

The following Database Roles exist in the database.

    • LuwareDataLoader – this role has permissions to execute integration jobs.
    • LuwareReporting – this role has permissions to execute all stored procedures in Reporting schema. This role is required to:
      • Get data through Excel Power Pivot Reports (version 2.3 – 3.2)
      • Get data through Excel Power Query Reports (any version)
      • Get data through Power BI Reports (any version)
      • Get data through SSRS Reports (any version)
    • LuwareGetDataset (Release 3.3 and above) role has permissions to execute [Reporting].[usp_GetDataSet]. This role is the most secure way to grant access to the report user. This role is required to get data through Power BI and Excel Power Query Reports (version 3.3 and above)
    • LuwareSystem – this role has permissions to run Luware application (PS Service).

Before configuring the role membership, it is important to understand the difference between SQL Server Logins and Users. Logins have the access to the server and Users have the access to the database. All work is done in the context of database, but to get to do the work, one needs to have firstly access to the SQL server. It means that to grant windows account permissions of a role, both Login and User are required.

Example: granting domain security group permissions to refresh data

The goal of this example is the permissions granting for a domain security group "Marketing" to get data in Excel Power Query and Power BI Reports using the "LuwareGetDataSet" role.

(lightbulb) This scenario - with small changes- is applicable for granting other database roles to Users and Groups.

  1. Open SSMS and navigate to Object Explorer > Expand SQL Server > Security > Logins.
  2. Execute the right click and select New Login… in the opened context menu.
  3. On General page, ensure that Windows authentication is selected.
  4. On General page, click Search button 
    1. Click Object Types and ensure that Groups are selected
    2. Click Locations and select Entire Directory
    3. Enter the object name, e.g.  "Marketing" and click Check Names
    4. Click OK on Select User, Service Account, or Group window
  5. On the User Mapping page, find database, on which permissions are required.

    1. In the list of "Users mapped to this login" on database row, tick the according "Map" checkbox
    2. In the role membership select "LuwareGetDataSet" role in Database role Membership
  6. Click OK