SELECT denied in AdventureWorksDW - SQL Server 2005

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.
LVL 44
zephyr_hex (Megan)DeveloperAsked:
Who is Participating?
 
zephyr_hex (Megan)DeveloperAuthor Commented:
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
 
zephyr_hex (Megan)DeveloperAuthor Commented:
i also wanted to add that when i'm in sql management studio, i can successfully execute a SELECT query on  dimProduct....
0
 
SQL_SERVER_DBACommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
zephyr_hex (Megan)DeveloperAuthor Commented:
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
 
modus_operandiCommented:
Closed, 500 points refunded.
modus_operandi
Community Support Moderator
0
 
modus_operandiCommented:
Closed, 500 points refunded.
modus_operandi
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.