Securing Your Cube Project - Getting Started with SQL Server 2012 Cube Development - page 224

tureWorks domain. Use the following steps to change the value for Amy Alberts
to the Windows user that you are currently logged on as. After this, you can imple-
ment dynamic security.
1. Open up SQL Server Management Studio.
2. Connect to the SQL Server instance that contains the AdventureWork-
sDW2012 database.
3. Run the following query to change the row containing the data for Amy Al-
berts :
update AdventureWorksDW2012..DimEmployee
set LoginID = SUSER_NAME() where
EmployeeKey = 290
select * from
AdventureWorksDW2012..DimEmployee where
EmployeeKey=290
4. Double-click on Employee.dim to open up the Employee dimension.
5. Drag the LoginId column from the DimEmployee table in the Data source
view and drop it in the Attribute field.
6. Right-click on the newly created Login ID attribute and choose Properties .
7. Change the value of the property called AttributeHierarchyVisible and set
it to False . This will hide the attribute from the users. You can still use it in
calculations.
8. Create a new role called Dynamic User Security.role .
9. Click on the Cubes tab and add Read access to the Adventure Works
DW2012 cube.
10. Click on the Dimension Data tab.
11. Find the Employee dimension and select the Parent Employee attribute.
12. In the Allowed member set , add the following code:
Filter([Employee].[Parent
Employee].Members,
[Employee].[Parent
Employee].Currentmember.Properties("Login
ID")=UserName)
13. Check the Enable Visual Totals checkbox. The newly created role should
look like the following: