what permission needed to run query that contains OpenRowset

Question

User154448021 posted

i have a MDX query as below

select * from OpenRowset('MSOLAP','DATA SOURCE=dev-as01; Initial Catalog=published cube with virtual cubes;','SELECT NON EMPTY { [Measures].[Last Month C], [Measures].[Last 3 Months C], [Measures].[Last 12 Months C] } ON COLUMNS, NON EMPTY { [KeyLoc CityState].[Citystate].ALLMEMBERS } ON ROWS FROM [Master Cube for SIMS asof 071017] where(([Bi Type Of Bill].[Bi Type Of Bill].[O]), ([Consistent Name].[Consistent Name].[borders]))')

within a stored procedure

i can run it successfully because i am an administrator of all the servers.

i have created a user with permissioin to login in with windows credentials (the user has to use windows credentials to access databases) to all the servers and permission to execute  stored procedures.  but when log in as the user, I got error message that says the user has insufficient permission to run this query. 

what is the minimum permission to run the above query?  the user is able to run other stored procedures that do not contatin OpenRowset.

I'll appreciate all your helps.

 

Answer 1

User840129657 posted

hi,

try this link it wil explain u..

http://msdn.microsoft.com/en-us/library/ms190312.aspx

http://msdn.microsoft.com/en-us/library/aa276850(SQL.80).aspx

http://doc.ddart.net/mssql/sql70/oa-oz_5.htm

See The "PERMISSIONS" paragraph in above links

Answer 2

User154448021 posted

 thanks for your reply.  but all the links say that OPENROWSET permissions are determined by the permissions of the user name that is being passed to the OLE DB provider while the user in my case has to use windows credentials.  Further more, i am not asking about the syntax.  I'm asking the required permission level  i need to to grant to a user.  

Answer 3

User-1878263194 posted

Sorry to resurrect this post, but I'm running into the same problem with permissions for OPENROWSET and I'm wondering if you have the answer.  Please let me know.