?
Solved

SELECT denied in AdventureWorksDW - SQL Server 2005

Posted on 2007-10-06
7
Medium Priority
?
802 Views
Last Modified: 2008-01-09
i am trying to work through an Analysis Services tutorial for SQL Server 2005.  everything has been peachy until i get to the point where i'm supposed to deploy my AdentureWorksDW cube.

when i attempt to deploy, i get the following error:
Error      3      OLE DB error: OLE DB or ODBC error: The SELECT permission was denied on the object 'DimProduct', database 'AdventureWorksDW', schema 'dbo'.; 42000.            0      0      

i have checked and i have a log in (my domain account) with roles "public" and "sysadmin", and i'm listed as a user under AdventureWorksDW->Security->Users with default schema dbo.
i checked schemas->dbo, and i see nothing explicitly listed.  is this the problem?  when i look through the other schemas in other databases, i see nothing explicitly granted...

when i look under AdventureWorksDW->Security->Roles, i don't see sysadmin listed... public is there... as are the other roles like db_datareader.  is this correct?

i also noticed when i look at the schemas owned by me in AdventureWorksDW->Security->users that nothing is checkmarked....  and db_denydatareader is listed...    are items enabled when checkmarked?  do i need to checkmark the non-deny items?

so, basically, i am a bit confused about what needs to be in place in order to fix this error...  what do i need to verify?

sql server 2005 enterprise
thanks.
0
Comment
Question by:zephyr_hex (Megan)
  • 3
  • 2
6 Comments
 
LVL 44

Author Comment

by:zephyr_hex (Megan)
ID: 20027865
i also wanted to add that when i'm in sql management studio, i can successfully execute a SELECT query on  dimProduct....
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20028065
are items enabled when checkmarked? Yes

i see nothing explicitly granted...

when i look under AdventureWorksDW->Security->Roles, i don't see sysadmin listed... public is there... as are the other roles like db_datareader.  is this correct? Yes.

I dont know if you know but this tells all
http://msdn2.microsoft.com/en-us/library/ms189121.aspx
0
 
LVL 44

Author Comment

by:zephyr_hex (Megan)
ID: 20028114
i understand what the different roles mean...
i'm just not sure why i'm getting this error.  at first, i thought it was the way i have things set up in sql management studio with regards to my user account / login / schema.

but since i can execute a select statement query on that table in the management studio, i'm wondering if it's not something else...  if it was a permissions issue with my user account, wouldn't i get an error when i execute a query in the management studio?

since the analysis services cube is using a data source OLE DB, perhaps the issue is there?  the tutorial walked me through setting up the data source... but said nothing about having to enable specific permissions in the database for that connection.

i'm certainly no sql expert so i might be heading down the wrong path here...

here is the tutorial: http://msdn2.microsoft.com/en-us/library/ms170208.aspx
here is where it says how to configure the data source: http://msdn2.microsoft.com/en-us/library/ms167105.aspx
and here is the step i'm stuck on (deploy):http://msdn2.microsoft.com/en-us/library/ms166576.aspx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 44

Accepted Solution

by:
zephyr_hex (Megan) earned 0 total points
ID: 20034696
i fixed this

the problem is that the data source impersonation is set to Service (which is required).  however, there was no user for nt authority/service.  i had to create a user for "Service" and then assign datareader role.  then deployment worked successfully.
0
 
LVL 1

Expert Comment

by:modus_operandi
ID: 20063038
Closed, 500 points refunded.
modus_operandi
Community Support Moderator
0
 
LVL 1

Expert Comment

by:modus_operandi
ID: 20063039
Closed, 500 points refunded.
modus_operandi
Community Support Moderator
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
What we learned in Webroot's webinar on multi-vector protection.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question