access to data in the cube

Hi,

i'm currently building a cube which data has a level of access depending who the logged in user is in the windows authentication. My cube's fact table is actually a VIEW with this line of code:

SELECT a.serviceid AS [service code], a.agentid AS [agent code], a.timeid AS date, a.talktime AS [talk time], a.wraptime AS [wrap time]
FROM dbo.factAgentActivity AS a INNER JOIN
dbo.factaccess AS b ON a.agentid = b.agent AND b.username = SUSER_NAME()

I used SUSER_NAME to get the name of the user currently logged in and compared it against the username table. If i open this VIEW in the SQL SERVER database, i can retrieve the right data that user will see. But when I used this in the Analysis Services and Browse the cube, the metrics and cubes have blank values. The same thing if i link my cube to excel.

Is my setup correct or im missing something here?

thanks in advance!

regards,

cherriesh

I'm not sure to understand but:

"SUSER_NAME()" return the current user logged in.

It means that when you process the cube "SUSER_NAME()" is replaced with the AS account.

If it is correct, how can AS dynamically change the user on browser time?

Maybe I'm missing something.

Francesco

|||if you already know what access they should have, why wouldn't you just create a role(s) within your SSAS database project and assign the NT Users (or NT Groups) accordingly. Depending on your storage mode for your cube once you process the cube the view wouldn't work anyway, that would just be used to perform the initial load of the cube.