In a data warehouse environment based on SQL Server, SSIS, SSAS MD and SSRS, a common request is to limit user permissions to specific data and measures. It’s common end users have only access to specific cost centres and/or specific (sensitive) measures. To limit the administrative work in managing SSAS roles and SSRS permissions, I have developed an automated mechanism where users automatically have access to the data and reports they are allowed to see. The permissions can be managed by configuring two master tables which can be administrated by any users, no specific technical knowledge is required.
- Access to Active Directory
- Employeenumbers are stored in Active Directory and correspond with Employeenumber in HR system.