executing job with non-admin account

hello,
i administer w2k server with sql server and analysis
service installed on it.
some developers use it to tackle their projects.
one of them needs to test job execution and can manage to
create, but is not being able to execute it.
he has registered the server on his desktop enterprise
manager with sql login that is db owner of a given
database.
i wouldn't want to give him sysadmin server role - one
that has more power than he needs.
Would someone knows an alternative way to grant him
rights to run job not being sysadmin?
TIA
Mas"Mas" <anonymous@.discussions.microsoft.com> wrote in message
news:0cbd01c3adf9$b428f1b0$a501280a@.phx.gbl...
> Would someone knows an alternative way to grant him
> rights to run job not being sysadmin?
Oracle? DB2?
One work-around I've found for the permissions problem for the Sqlagent (so
you don't get that infernal database "guest" permissions error) is to set up
the job steps as OS commands that call dtsrun & osql. Just set the user up
as an operator, he'll then be able to run the xp_* sys. procs to run the OS
commands from the job manager, with no SA privileges to worry about. But
talk about convoluted! How 'bout a real job/task security model, eh
Microsoft?
You're not the first to complain about this on this forum, it's frequent
question. MS's security model is - well - unique in the industry in this
regard. The answer in these fora so far has been "It's supposed to work that
way." Maybe one of the MVPs who hang out here might write an FAQ that
explains how "It's supposed to work that way" (along with MS's idea of error
handling and load balanced clusters). Another Yukon feature to wait/pay for?
Tell you what... Max-DB/SAP-DB & PostgreSQL are coming up close behind and
are going to pass MS if MS doesn't watch out.
--j|||Hi, many thanks! I'll try to workout this way.
Mas
>--Original Message--
>"Mas" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0cbd01c3adf9$b428f1b0$a501280a@.phx.gbl...
>> Would someone knows an alternative way to grant him
>> rights to run job not being sysadmin?
>Oracle? DB2?
>One work-around I've found for the permissions problem
for the Sqlagent (so
>you don't get that infernal database "guest" permissions
error) is to set up
>the job steps as OS commands that call dtsrun & osql.
Just set the user up
>as an operator, he'll then be able to run the xp_* sys.
procs to run the OS
>commands from the job manager, with no SA privileges to
worry about. But
>talk about convoluted! How 'bout a real job/task
security model, eh
>Microsoft?
>You're not the first to complain about this on this
forum, it's frequent
>question. MS's security model is - well - unique in the
industry in this
>regard. The answer in these fora so far has been "It's
supposed to work that
>way." Maybe one of the MVPs who hang out here might
write an FAQ that
>explains how "It's supposed to work that way" (along
with MS's idea of error
>handling and load balanced clusters). Another Yukon
feature to wait/pay for?
>Tell you what... Max-DB/SAP-DB & PostgreSQL are coming
up close behind and
>are going to pass MS if MS doesn't watch out.
>--j
>
>.
>

executing analysis services query via openrowset

Can you kindly tell me what settings are required to execute an MDX statement via openrowset.

Currently I am executing it by impersonating my user (sql user) as "sa" account and everything goes ok.

The following is the query i am using

SELECT "[Dim Agent].[Dim Agent].[Dim Agent].[MEMBER_CAPTION]" AS AgentNumber,

"[Dim Application].[Dim Application].[Dim Application].[MEMBER_CAPTION]" AS ApplicationId,

ISNULL("[Dim Event].[Dim Event].&[1]",0) AS PropertyViews,

ISNULL("[Dim Event].[Dim Event].&[2]",0) AS ScheduleAShowing,

ISNULL("[Dim Event].[Dim Event].&[3]",0) AS ContactMe

FROM OpenRowset('MSOLAP.3',

'DATASOURCE=RIGGINS2\LFDB2; Initial Catalog=PicassoLnfWebMetric;Integrated Security=SSPI',

'SELECT {[Dim Event].[Dim Event].&[1],[Dim Event].[Dim Event].&[2],[Dim Event].[Dim Event].&[3]} ON COLUMNS,

NON EMPTY([Dim Agent].[Dim Agent].[Dim Agent] * [Dim Application].[Dim Application].[Dim Application]) ON ROWS

FROM [Lnf Web Metric] WHERE {([Dim Date].[Date].&[2007-05-20T00:00:00]:[Dim Date].[Date].&[2007-05-28T00:00:00],[Dim Agent].[Agent Status].&), ([Dim Date].[Date].&[2007-05-20T00:00:00]:[Dim Date].[Date].&[2007-05-28T00:00:00],[Dim Agent].[Agent Status].[All].UNKNOWNMEMBER)}')

Can you kindly let me know what do i need to do to run this query by impersonating as some windows account?

Warm regards,

Sudhir

I don't think you can do this using OpenRowset(). You could try setting up a linked server and then using OpenQuery(). There are options when you set up a linked server that let you specify a security context. If your SQL and AS services are on the same machine you should be able to get this working, if they are on separate machines you would need to configure Kerberos authentication. (There are various whitepapers available on how to do this)|||can you redirect me to some whitepapers?|||

On configuring Kerberos? sure http://support.microsoft.com/kb/917409 & http://sqljunkies.com/WebLog/mosha/archive/2005/01/25/6905.aspx - specifically relates to AS2005

On adding a linked server http://msdn2.microsoft.com/en-us/library/aa936675(SQL.80).aspx, you also have to make sure with AS that the provider is set to run In-process.