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.

Access SQL Server from Stand-Alone PC with Windows Authentication

I have a PC that is not joined to the domain. I want to be able to access
our SQL Server from it, but it is my active directory domain account that ha
s
access. How can I gain access to the SQL Server via Enterprise Manager or
Query Analyzer?Hello,
If the machine is in a workgroup, SQL server machine is in the domain, make
sure you can ping the SQL server machine from the machine you log on
successfully via IP address or machine name.
Create a same Local user account both on the machine and the SQL server
machine. The Local user account and password on both machines need to be
same . On the SQL server machine, add the Local user account as the SQL
server logins and grant the user right to access the database. By doing so,
you can access the SQL server via Query Analyzer with Windows
authentication.
Or you can access the SQL server using a SQL logins via Query Analyzer with
SQL authentication.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

access sql server across domain thru windows authentication

Hi,
I have a sql server in a domain which is different than the one I log into,
and thats server has Windows authenticatrion, I have a domain account in the domain where sql is running, now how do I provide that domain acc info en EM to connect to that sql server. If I just select windows authenication while registering that server it gives me Cannot generate SSPI context error.

Any help will be appricaited.

Tia,
ParthYou can not do this from EM, as far as I know!
Or if you can, I would like to know that, too.

Best regards!

Access denied when Windows password changed

I have a local installation of SQL server (SqlServer 2000). The server
registration uses Windows authentication. When I change my Windows domain
password, I can`t start the server (Connect() failed).
As an emergency soloution, I changed my password back to the old one, but
this is not q real soloution.
What can I do to change password and still be able to connect ?
TIA
Boaz Ben-Porat
Milestone SystemsHi Boaz,
Seems to me you need to change the MSSQLSERVER and SQLSERVERAGENT services
passwords.
You can get to these services from the administrtive tools (Start Menu >
Settings > Control Panels > Administritive Tools > Services).
Locate and double click on the services and in the "LogOn" tab you can
change the activation passwords.
I hope this will solve your problem.
Regards,
Shachaf Ben-Tzur
Xpert Israel
"Boaz Ben-Porat" <bbp@.milestone.dk> wrote in message
news:e04BoCGeEHA.236@.tk2msftngp13.phx.gbl...
> I have a local installation of SQL server (SqlServer 2000). The server
> registration uses Windows authentication. When I change my Windows domain
> password, I can`t start the server (Connect() failed).
> As an emergency soloution, I changed my password back to the old one, but
> this is not q real soloution.
> What can I do to change password and still be able to connect ?
> TIA
> Boaz Ben-Porat
> Milestone Systems
>

Access Denied when setting up a Subscriber for replication

i verified that the server settings and authentication is
the same for both servers/
i am trying to configure a windows 2003 server running sql
server 2000 to be a subscriber to a publisher runnign
win2k and sql 2000.
when I try to register the server, i get access denied.
any thoughts?
thanks
Jeff,
can you test network connectivity using PING and NET VIEW
(http://www.microsoft.com/resources/d...2003/standard/
proddocs/en-us/Default.asp?url=/resources/documentation/WindowsServ/2003/sta
ndard/proddocs/en-us/sag_TCPIP_pro_PingConnect.asp).
Can you also use QA on hte subscriber and test this tool, using a SQL user
(assuming mixed mode authentication).
Can you check that the SQL Server Service is started on the publisher
(sounds obvious but it's better if I ask).
Regards,
Paul Ibison
|||Hi Paul,
i can connect to the server using QA and specifying the IP
of the server. I can also ping using host name and IP.
thanjks

>--Original Message--
>Jeff,
>can you test network connectivity using PING and NET VIEW
>
(http://www.microsoft.com/resources/d...tion/WindowsSe
rv/2003/standard/
>proddocs/en-us/Default.asp?
url=/resources/documentation/WindowsServ/2003/sta
>ndard/proddocs/en-us/sag_TCPIP_pro_PingConnect.asp).
>Can you also use QA on hte subscriber and test this tool,
using a SQL user
>(assuming mixed mode authentication).
>Can you check that the SQL Server Service is started on
the publisher
>(sounds obvious but it's better if I ask).
>Regards,
>Paul Ibison
>
>.
>
|||Jeff,
can you connect using QA and the host name with trusted / non-trusted
security (logging into windows using the same account as the sql server
agent).
If you can connect using trusted security and host name then check that the
Account is in the PAL for the publication.
Finally, I did read in a thread that DTC needs enabling, which it isn't by
default in Win 2003
(http://support.microsoft.com/default...5bLN%5d;817064). I
understand the need for DTC in immediate updating subscribers but haven't
testes whether this thread was correct or not. Anyway, if it's not enabled
and the above comments don't lead to the solution, please try this also.
HTH,
Paul Ibison
|||I'd check your Client Network Utility Settings and verify that they are
pointing to the correct server.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u1BBBRLaEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Jeff,
> can you connect using QA and the host name with trusted / non-trusted
> security (logging into windows using the same account as the sql server
> agent).
> If you can connect using trusted security and host name then check that
the
> Account is in the PAL for the publication.
> Finally, I did read in a thread that DTC needs enabling, which it isn't by
> default in Win 2003
> (http://support.microsoft.com/default...5bLN%5d;817064). I
> understand the need for DTC in immediate updating subscribers but haven't
> testes whether this thread was correct or not. Anyway, if it's not enabled
> and the above comments don't lead to the solution, please try this also.
> HTH,
> Paul Ibison
>
|||Hilary and Paul,
i have looked at the client network util. and verified
they are pointing to the right server.
The issue now is, I cannot even register the server from
the Publisher in the ACL list. This is getting weirder by
the minute. I can verify connectivity by ping with no
issues.
In the Client util, i configure the server name on the
alias and the IP and also specify the port the SQL server
is listening on at the Subscriber. So at this point, i am
not sure what the issue is...
thanks for your help

>--Original Message--
>I'd check your Client Network Utility Settings and verify
that they are[vbcol=seagreen]
>pointing to the correct server.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
>news:u1BBBRLaEHA.4092@.TK2MSFTNGP11.phx.gbl...
trusted / non-trusted[vbcol=seagreen]
as the sql server[vbcol=seagreen]
then check that[vbcol=seagreen]
>the
enabling, which it isn't by[vbcol=seagreen]
5bLN%5d;817064). I[vbcol=seagreen]
subscribers but haven't[vbcol=seagreen]
if it's not enabled[vbcol=seagreen]
please try this also.
>
>.
>