Connect to Analysis Services with specific username

Hello,

We are trying to develop an ASP .Net web application that connects to an SQL Server 2005 analysis services and executes MDX statements. We want the connection to be made using a specific user name that we specify in the code rather than having the web application inherit the logged on domain user account.

We tried to specifiy the user name in the connection string as follows, but it is still taking the logged in user account rather than the one specified.

Microsoft.AnalysisServices.Server s = new Microsoft.AnalysisServices.Server();

s.Connect("Provider=MSOLAP.3;Password=123;Persist Security Info=True;User ID=domain\username;Data Source=<server ip>;Initial Catalog=<analysis DB>");

Is this possible?

Note that if we are using the OLEDB 9.0 provider for Analysis Services to connect an excel sheet to the analysis database we can easily specify the user name and password, so is there a similar way to do the same from a .Net application?

Any feedback is appreciated, thanks,

Grace

If you want to use a Windows Indentity, then you can′t. For other connection options refer to the connection properties which are shown e.g. here:

http://www.ssas-info.com/ssas_articles/ssas_articles/analysis_services_2000_and_2005_connection_string_properties.html

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens,

The link helped a lot. We were able to connect using specific Roles and that is what we were looking for exactly but didn't know it exists.

Grace

Confused by sp_rename.

I am confused by the rules for the sp_rename stored proc and it's
interaction with other statements . It seems that is provide owner.name
for the second parameter (instead of just name), the SELECT statement
afterwards simply will tell me that the table does not exist. Am I
missing something really simple?
For instance, the following works fine:
sp_rename N'[dbo].[tmp_sfx_tbl_003_01]', N'tbl_003_01'
select * from tbl_003_01
However, the statements below do not work:
sp_rename 'tmp_sfx_tbl_003_01', '[dbo].[tbl_003_01]'
select * from tbl_003_01 --returns Invalid object name 'dbo.tbl_003_01'.
ThanksSp_rename does not allow for owner/schema qualified naming for new name. Thi
s would allow you to
change owner/schema with sp_rename, so it will interpret that part as the ne
w name. What you did was
to rename the table to:
[dbo].[a1]
Above is the name without any delimiters or owner/schema. So, to use it, hav
ing delimiters and
owner/schema, you use:
[dbo].[[dbo]].[a1]]]
To see what happend, try below:
Use tempdb
CREATE TABLE dbo.a(c1 int)
GO
EXEC sp_rename a, '[dbo].[a1]'
GO
SELECT name FROM sysobjects WHERE name LIKE '%a1%'
SELECT * FROM [dbo].[[dbo]].[a1]]]
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.net> wrote in message news:%232VAclflHHA.4188@.TK2MSFTNGP02.phx.gbl.
.
>I am confused by the rules for the sp_rename stored proc and it's interacti
on with other statements
>. It seems that is provide owner.name for the second parameter (instead of
just name), the SELECT
>statement afterwards simply will tell me that the table does not exist. Am
I missing something
>really simple?
> For instance, the following works fine:
> sp_rename N'[dbo].[tmp_sfx_tbl_003_01]', N'tbl_003_01'
> select * from tbl_003_01
> However, the statements below do not work:
> sp_rename 'tmp_sfx_tbl_003_01', '[dbo].[tbl_003_01]'
> select * from tbl_003_01 --returns Invalid object name 'dbo.tbl_003_01'.
>
> Thanks|||Tibor Karaszi wrote:
> Sp_rename does not allow for owner/schema qualified naming for new name.
> This would allow you to change owner/schema with sp_rename, so it will
> interpret that part as the new name. What you did was to rename the
> table to:
> [dbo].[a1]
> Above is the name without any delimiters or owner/schema. So, to use it,
> having delimiters and owner/schema, you use:
> [dbo].[[dbo]].[a1]]]
>
> To see what happend, try below:
>
> Use tempdb
> CREATE TABLE dbo.a(c1 int)
> GO
> EXEC sp_rename a, '[dbo].[a1]'
> GO
> SELECT name FROM sysobjects WHERE name LIKE '%a1%'
> SELECT * FROM [dbo].[[dbo]].[a1]]]
So how would I assign ownership as a part of the rename? Say I want to
rename dbo.temp_a1 to rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
Thanks|||Look up "alter schema ... transfer" in BOL.
"Frank Rizzo" <none@.none.com> wrote in message
news:ejlnomklHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Tibor Karaszi wrote:
>
> So how would I assign ownership as a part of the rename? Say I want to
> rename dbo.temp_a1 to rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
> Thanks|||> So how would I assign ownership as a part of the rename? Say I want to rename dbo.temp_a1
to
> rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
You don't. You do it in two separate operations.
Chancing object owner in 2000 is done using sp_changeobjectowner. In 2005, t
he command depends on
whether you want to change schema or owner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:ejlnomklHHA.4772@.TK2MSFTNGP05.phx.gbl...

> Tibor Karaszi wrote:
>
> So how would I assign ownership as a part of the rename? Say I want to re
name dbo.temp_a1 to
> rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
> Thanks

Confused by sp_rename.

I am confused by the rules for the sp_rename stored proc and it's
interaction with other statements . It seems that is provide owner.name
for the second parameter (instead of just name), the SELECT statement
afterwards simply will tell me that the table does not exist. Am I
missing something really simple?
For instance, the following works fine:
sp_rename N'[dbo].[tmp_sfx_tbl_003_01]', N'tbl_003_01'
select * from tbl_003_01
However, the statements below do not work:
sp_rename 'tmp_sfx_tbl_003_01', '[dbo].[tbl_003_01]'
select * from tbl_003_01 --returns Invalid object name 'dbo.tbl_003_01'.
Thanks
Sp_rename does not allow for owner/schema qualified naming for new name. This would allow you to
change owner/schema with sp_rename, so it will interpret that part as the new name. What you did was
to rename the table to:
[dbo].[a1]
Above is the name without any delimiters or owner/schema. So, to use it, having delimiters and
owner/schema, you use:
[dbo].[[dbo]].[a1]]]
To see what happend, try below:
Use tempdb
CREATE TABLE dbo.a(c1 int)
GO
EXEC sp_rename a, '[dbo].[a1]'
GO
SELECT name FROM sysobjects WHERE name LIKE '%a1%'
SELECT * FROM [dbo].[[dbo]].[a1]]]
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.net> wrote in message news:%232VAclflHHA.4188@.TK2MSFTNGP02.phx.gbl...
>I am confused by the rules for the sp_rename stored proc and it's interaction with other statements
>. It seems that is provide owner.name for the second parameter (instead of just name), the SELECT
>statement afterwards simply will tell me that the table does not exist. Am I missing something
>really simple?
> For instance, the following works fine:
> sp_rename N'[dbo].[tmp_sfx_tbl_003_01]', N'tbl_003_01'
> select * from tbl_003_01
> However, the statements below do not work:
> sp_rename 'tmp_sfx_tbl_003_01', '[dbo].[tbl_003_01]'
> select * from tbl_003_01 --returns Invalid object name 'dbo.tbl_003_01'.
>
> Thanks
|||Tibor Karaszi wrote:
> Sp_rename does not allow for owner/schema qualified naming for new name.
> This would allow you to change owner/schema with sp_rename, so it will
> interpret that part as the new name. What you did was to rename the
> table to:
> [dbo].[a1]
> Above is the name without any delimiters or owner/schema. So, to use it,
> having delimiters and owner/schema, you use:
> [dbo].[[dbo]].[a1]]]
>
> To see what happend, try below:
>
> Use tempdb
> CREATE TABLE dbo.a(c1 int)
> GO
> EXEC sp_rename a, '[dbo].[a1]'
> GO
> SELECT name FROM sysobjects WHERE name LIKE '%a1%'
> SELECT * FROM [dbo].[[dbo]].[a1]]]
So how would I assign ownership as a part of the rename? Say I want to
rename dbo.temp_a1 to rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
Thanks
|||Look up "alter schema ... transfer" in BOL.
"Frank Rizzo" <none@.none.com> wrote in message
news:ejlnomklHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Tibor Karaszi wrote:
>
> So how would I assign ownership as a part of the rename? Say I want to
> rename dbo.temp_a1 to rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
> Thanks
|||> So how would I assign ownership as a part of the rename? Say I want to rename dbo.temp_a1 to
> rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
You don't. You do it in two separate operations.
Chancing object owner in 2000 is done using sp_changeobjectowner. In 2005, the command depends on
whether you want to change schema or owner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:ejlnomklHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Tibor Karaszi wrote:
>
> So how would I assign ownership as a part of the rename? Say I want to rename dbo.temp_a1 to
> rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
> Thanks

Confused by sp_rename.

I am confused by the rules for the sp_rename stored proc and it's
interaction with other statements . It seems that is provide owner.name
for the second parameter (instead of just name), the SELECT statement
afterwards simply will tell me that the table does not exist. Am I
missing something really simple?
For instance, the following works fine:
sp_rename N'[dbo].[tmp_sfx_tbl_003_01]', N'tbl_003_01'
select * from tbl_003_01
However, the statements below do not work:
sp_rename 'tmp_sfx_tbl_003_01', '[dbo].[tbl_003_01]'
select * from tbl_003_01 --returns Invalid object name 'dbo.tbl_003_01'.
ThanksSp_rename does not allow for owner/schema qualified naming for new name. This would allow you to
change owner/schema with sp_rename, so it will interpret that part as the new name. What you did was
to rename the table to:
[dbo].[a1]
Above is the name without any delimiters or owner/schema. So, to use it, having delimiters and
owner/schema, you use:
[dbo].[[dbo]].[a1]]]
To see what happend, try below:
Use tempdb
CREATE TABLE dbo.a(c1 int)
GO
EXEC sp_rename a, '[dbo].[a1]'
GO
SELECT name FROM sysobjects WHERE name LIKE '%a1%'
SELECT * FROM [dbo].[[dbo]].[a1]]]
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.net> wrote in message news:%232VAclflHHA.4188@.TK2MSFTNGP02.phx.gbl...
>I am confused by the rules for the sp_rename stored proc and it's interaction with other statements
>. It seems that is provide owner.name for the second parameter (instead of just name), the SELECT
>statement afterwards simply will tell me that the table does not exist. Am I missing something
>really simple?
> For instance, the following works fine:
> sp_rename N'[dbo].[tmp_sfx_tbl_003_01]', N'tbl_003_01'
> select * from tbl_003_01
> However, the statements below do not work:
> sp_rename 'tmp_sfx_tbl_003_01', '[dbo].[tbl_003_01]'
> select * from tbl_003_01 --returns Invalid object name 'dbo.tbl_003_01'.
>
> Thanks|||Tibor Karaszi wrote:
> Sp_rename does not allow for owner/schema qualified naming for new name.
> This would allow you to change owner/schema with sp_rename, so it will
> interpret that part as the new name. What you did was to rename the
> table to:
> [dbo].[a1]
> Above is the name without any delimiters or owner/schema. So, to use it,
> having delimiters and owner/schema, you use:
> [dbo].[[dbo]].[a1]]]
>
> To see what happend, try below:
>
> Use tempdb
> CREATE TABLE dbo.a(c1 int)
> GO
> EXEC sp_rename a, '[dbo].[a1]'
> GO
> SELECT name FROM sysobjects WHERE name LIKE '%a1%'
> SELECT * FROM [dbo].[[dbo]].[a1]]]
So how would I assign ownership as a part of the rename? Say I want to
rename dbo.temp_a1 to rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
Thanks|||Look up "alter schema ... transfer" in BOL.
"Frank Rizzo" <none@.none.com> wrote in message
news:ejlnomklHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Tibor Karaszi wrote:
>> Sp_rename does not allow for owner/schema qualified naming for new name.
>> This would allow you to change owner/schema with sp_rename, so it will
>> interpret that part as the new name. What you did was to rename the table
>> to:
>> [dbo].[a1]
>> Above is the name without any delimiters or owner/schema. So, to use it,
>> having delimiters and owner/schema, you use:
>> [dbo].[[dbo]].[a1]]]
>>
>> To see what happend, try below:
>>
>> Use tempdb
>> CREATE TABLE dbo.a(c1 int)
>> GO
>> EXEC sp_rename a, '[dbo].[a1]'
>> GO
>> SELECT name FROM sysobjects WHERE name LIKE '%a1%'
>> SELECT * FROM [dbo].[[dbo]].[a1]]]
>
> So how would I assign ownership as a part of the rename? Say I want to
> rename dbo.temp_a1 to rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
> Thanks|||> So how would I assign ownership as a part of the rename? Say I want to rename dbo.temp_a1 to
> rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
You don't. You do it in two separate operations.
Chancing object owner in 2000 is done using sp_changeobjectowner. In 2005, the command depends on
whether you want to change schema or owner.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:ejlnomklHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Tibor Karaszi wrote:
>> Sp_rename does not allow for owner/schema qualified naming for new name. This would allow you to
>> change owner/schema with sp_rename, so it will interpret that part as the new name. What you did
>> was to rename the table to:
>> [dbo].[a1]
>> Above is the name without any delimiters or owner/schema. So, to use it, having delimiters and
>> owner/schema, you use:
>> [dbo].[[dbo]].[a1]]]
>>
>> To see what happend, try below:
>>
>> Use tempdb
>> CREATE TABLE dbo.a(c1 int)
>> GO
>> EXEC sp_rename a, '[dbo].[a1]'
>> GO
>> SELECT name FROM sysobjects WHERE name LIKE '%a1%'
>> SELECT * FROM [dbo].[[dbo]].[a1]]]
>
> So how would I assign ownership as a part of the rename? Say I want to rename dbo.temp_a1 to
> rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
> Thanks