Executing MDX StoredProcedures using AdomdClient.dll

Hi fellow developers,

At the moment, i have a SSAS 2005 installed and a working cube on it. Everything is working fine. However i want to have an ASP.NET Page to access some of the data in the cube and present it.

Well this would be no problem at all, if i use the AdomdClient and send queries over it, but this destroy my layer architecture. I dont want any Queries in my Application, but in the SQL Server.

When i used a sql server database i always used stored procedures to access the data because of security and consistence.

Now is there a possible way to create MDX Stored Procedures and execute them with the Adomd API like i did with sql server databases?

Or other clean solutions like creating mdx libraries on server and access it with the client?

I appreciate your help.

Sincerely

David

PS: If you find any gramatically mistakes, keep em :-))

Moving to SQL Server Analysis Services forum.

Thanks,
Sarah

|||So far there is no support for stored procedures. There is support for parameterized queries though.

So you do not want ASP.NET code contain explicit text of MDX queries, right? What is your real goal here? Do you want to hide the text of the MDX queries from the writer of ASP.NET code?

How about creating some .NET component accepting a reference to the connection object, a name of the "stored procedure" and list of parameters? The component would fetch the real text from some other store, use the passed connection and parameters and pass the query to the server. Then return the results back to the client.

This is really not that useful, but maybe it will cover your goal.|||

Hi Andrew

Thanks for your answer.

Well the goal would be that, other applications could access the same Queries, without copying the Queries in their application code. However we decided in the meanwhile that we use the queries in the code, because we dont have any time to search other clean solutions :-)

But still i am interested in a clean splitted Query Logic -> Application Logic solution. If anyone knows? Please let me know.

Sincerelly

David

|||By other applications you probably mean those also developed by you, right? The applications could load the component. It could also accept references to a store of MDX queries so that different scenarios would load different stores and keep the queries in memory (so that not to have performance problems in multithreaded scenarios).sql

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
>
>.
>

Execute Permissions on 400 SPROCs

Our developers are rolling out an app with 400 new SPROCS. All data access i
s
done through them. I need to give a single user execute permissions on all
400 SPROCS. It would be easiest to just give the user execute permsissions o
n
all stored procs and remove access from the few that don't apply.
Is there a fast way to do this?The preferred way is to create a database User Role and provide execute
permissions to the User Role. And the same applies for creating a user Role
for DENY EXECUTE.
Then as users come and go, they only have to be added to or removed from the
User Role. The 'Best Practice' is to add the something like the following to
each stored procedure script file (You do have them in source
control -right?).
GRANT EXECUTE ON {StoredProcedureName} TO {UserRole}
And if necessary,
DENY EXECUTE ON {StoredProcedureName} TO {DenyUserRole}
Then, when the files are run on any server, the permissions are correct.
There are some stored procedures for which it is probably not a good idea to
provide users EXECUTE permissions. It is much better to explicitly grant
permissions to each stored procedure rather than use 'blanket' permissions
for all objects. I would much rather know that permissions were explicit
provided than accidentally supplied due to 'sloppiness'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82DEA919-4FB4-4BE1-B495-3FB1722122BB@.microsoft.com...
> Our developers are rolling out an app with 400 new SPROCS. All data access
> is
> done through them. I need to give a single user execute permissions on all
> 400 SPROCS. It would be easiest to just give the user execute permsissions
> on
> all stored procs and remove access from the few that don't apply.
> Is there a fast way to do this?|||Dan
SELECT 'GRANT EXECUTE ON [' + USER_NAME(uid) + '].[' + name + '] TO
' +
'[UserNameHere]'
FROM sysobjects
WHERE
type = 'P'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(USER_
NAME(uid)) + '.' +
QUOTENAME(name)), 'IsMSShipped') = 0
--Run the output in QA
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82DEA919-4FB4-4BE1-B495-3FB1722122BB@.microsoft.com...
> Our developers are rolling out an app with 400 new SPROCS. All data access
> is
> done through them. I need to give a single user execute permissions on all
> 400 SPROCS. It would be easiest to just give the user execute permsissions
> on
> all stored procs and remove access from the few that don't apply.
> Is there a fast way to do this?

EXECUTE permission denied on object 'sp_sdidebug', database 'maste

What permissions need to be granted to developers to allow them to debug a
procedure in query analyzer.
Thanks
RonRon,
Try adding the user to the Master system database and then GRANT permissions
to execute the sp_sdidebug system stored procedure.
See also:
http://msdn.microsoft.com/library/d...>
ols_5cfm.asp
HTH
Jerry
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DCC04B2B-AA46-41F2-813F-1E5DC1C16DF8@.microsoft.com...
> What permissions need to be granted to developers to allow them to debug a
> procedure in query analyzer.
> Thanks
> Ron

EXECUTE permission denied on object 'sp_sdidebug', database 'maste

What permissions need to be granted to developers to allow them to debug a
procedure in query analyzer.
Thanks
Ron
Ron,
Try adding the user to the Master system database and then GRANT permissions
to execute the sp_sdidebug system stored procedure.
See also:
http://msdn.microsoft.com/library/de...tools_5cfm.asp
HTH
Jerry
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DCC04B2B-AA46-41F2-813F-1E5DC1C16DF8@.microsoft.com...
> What permissions need to be granted to developers to allow them to debug a
> procedure in query analyzer.
> Thanks
> Ron

EXECUTE permission denied on object 'sp_sdidebug', database 'maste

What permissions need to be granted to developers to allow them to debug a
procedure in query analyzer.
Thanks
RonRon,
Try adding the user to the Master system database and then GRANT permissions
to execute the sp_sdidebug system stored procedure.
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servtools_5cfm.asp
HTH
Jerry
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DCC04B2B-AA46-41F2-813F-1E5DC1C16DF8@.microsoft.com...
> What permissions need to be granted to developers to allow them to debug a
> procedure in query analyzer.
> Thanks
> Ron