|
Home > Archive > MS SQL Data Warehousing > October 2005 > what kind of user can update CUBE (Aggregations)?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
what kind of user can update CUBE (Aggregations)?
|
|
| Word 2003 memory Leakage 2005-10-17, 1:24 pm |
| I would like to add more error infor for this problem.
When I set up connection string for Data Source for my Cube, I put a SQL
server user for the connection string, which allows me to access to the data
warehouse and then process the cube.
There is also a tab named: Impersonation information, where I do not know
what to put. But I tried the sql user and a user to the cube database (which
is a domain user and is ok to run MDX query). for both I could not ran
through aggregation design, and get the error dialog as:
"Word 2003 memory Leakage" wrote:
> I can use windows domain user to update cube (dimensions, mesures, calculated
> memebers) but it did not work when I tried to process aggregations to improve
> the performance.
>
> It seems this action requires different permisssion. Sometimes, it says you
> need serveradmin, sometimes error password.
>
> What kind of SQL Server dataware house user can do this?
>
> Or some good documents on this?
>
> Thanks,
>
> Guangming
| |
| Word 2003 memory Leakage 2005-10-17, 1:24 pm |
| I can use windows domain user to update cube (dimensions, mesures, calculated
memebers) but it did not work when I tried to process aggregations to improve
the performance.
It seems this action requires different permisssion. Sometimes, it says you
need serveradmin, sometimes error password.
What kind of SQL Server dataware house user can do this?
Or some good documents on this?
Thanks,
Guangming
| |
| Word 2003 memory Leakage 2005-10-17, 1:24 pm |
| I guess I solved this problem but not sure it is the right practise.
For the connection string, the user should with serveradmin role in the data
warehouse db; for the impersonation information, the user should be a domain
user to the cube db.
"Word 2003 memory Leakage" wrote:
[color=darkred]
> I would like to add more error infor for this problem.
>
> When I set up connection string for Data Source for my Cube, I put a SQL
> server user for the connection string, which allows me to access to the data
> warehouse and then process the cube.
>
> There is also a tab named: Impersonation information, where I do not know
> what to put. But I tried the sql user and a user to the cube database (which
> is a domain user and is ok to run MDX query). for both I could not ran
> through aggregation design, and get the error dialog as:
>
>
>
> "Word 2003 memory Leakage" wrote:
>
| |
|
| It doesn't sound right that you need serveradmin for the db connection. I
would have thought read-only access to the database would be enough.
The account that you want to process cubes with needs to be in the OLAP
Administrator group on the server with Analysis Services running. You could
also give this account access to your SQL database, or specify a different
account in your data source.
Administering the cubes and accessing the underlying database are two
different things.
Wreck.
"Word 2003 memory Leakage" wrote:
[color=darkred]
> I guess I solved this problem but not sure it is the right practise.
>
> For the connection string, the user should with serveradmin role in the data
> warehouse db; for the impersonation information, the user should be a domain
> user to the cube db.
>
> "Word 2003 memory Leakage" wrote:
>
| |
| Word 2003 memory Leakage 2005-10-17, 1:24 pm |
| This time I believe the problem is solved.
To summary,
1, it is right that Access to cube (AS) db is different from to Data
warehouse. Each uses its own user acct.
2, for access to DW in Cube, user acct is set in connection string and
impersonation info in the properties dialog for a Data source.
3, after you run an AS solution in VS studio, I guess you can not change the
role of this user in VS studio.
The user needs to be in server role, which is not shown in AS solution.
Actually, server role seems is part of an AS instance not an AS Cube. So go
to MS sql svr management studio, right click the instance and go to the
security and add the user to the server role.
Then you can log on as this user to process the cube and the aggregation.
Hopefully, it is clear and right.
Guangming
"Word 2003 memory Leakage" wrote:
[color=darkred]
> I would like to clarify what I did so far to help you guys understand the
> problem.
>
> After I ran the aggregation design wizard to design, if I processed, I got
> error window with info:
>
> The ImpersonationInfo for datasource 'HsseDW_DSN' contains an
> ImpersonationMode that can only be used by a server administrator, .
>
> To restart the process, resolve the problem, and them click Start.
>
> I did add the user who access to the cube into OLAP Administrator Group on
> AS server.
>
> BTW, one question: does the normal process (right click a cube to process)
> create aggregation I designed using the wizard?
>
> Is there an easy to check if there are some aggregation in visual studio or
> ms sql svr management studio?
>
> Thanks,
>
>
> Guangming
>
> "Word 2003 memory Leakage" wrote:
>
| |
| Word 2003 memory Leakage 2005-10-17, 1:24 pm |
| I did not fix the problem.
I got several things to clarify:
We can create a user acct in SQL Server db. It seems we can not do this in
AS db
So all users in AS db are windows user names in the format of domain name \
account name.
the user accessing to AS db is in the admin role. Is this role same as the
OLAP Administrator group you mentioned?
Is there some good documents on this as it is a critical issue I need to know?
Thanks,
Guangming
"Wreck" wrote:
[color=darkred]
> It doesn't sound right that you need serveradmin for the db connection. I
> would have thought read-only access to the database would be enough.
>
> The account that you want to process cubes with needs to be in the OLAP
> Administrator group on the server with Analysis Services running. You could
> also give this account access to your SQL database, or specify a different
> account in your data source.
>
> Administering the cubes and accessing the underlying database are two
> different things.
>
> Wreck.
>
>
> "Word 2003 memory Leakage" wrote:
>
| |
| Word 2003 memory Leakage 2005-10-17, 1:24 pm |
| I would like to clarify what I did so far to help you guys understand the
problem.
After I ran the aggregation design wizard to design, if I processed, I got
error window with info:
The ImpersonationInfo for datasource 'HsseDW_DSN' contains an
ImpersonationMode that can only be used by a server administrator, .
To restart the process, resolve the problem, and them click Start.
I did add the user who access to the cube into OLAP Administrator Group on
AS server.
BTW, one question: does the normal process (right click a cube to process)
create aggregation I designed using the wizard?
Is there an easy to check if there are some aggregation in visual studio or
ms sql svr management studio?
Thanks,
Guangming
"Word 2003 memory Leakage" wrote:
[color=darkred]
> I did not fix the problem.
>
> I got several things to clarify:
>
> We can create a user acct in SQL Server db. It seems we can not do this in
> AS db
>
> So all users in AS db are windows user names in the format of domain name \
> account name.
>
> the user accessing to AS db is in the admin role. Is this role same as the
> OLAP Administrator group you mentioned?
>
> Is there some good documents on this as it is a critical issue I need to know?
>
> Thanks,
>
> Guangming
>
>
> "Wreck" wrote:
>
|
|
|
|
|