Can not retrieve data from a sql view

Question

Hello,

I am trying to go from an Excel workbook using Power Pivot to get external data from a sql view.

Here is an image of the error.

I've been granted more and more rights to my account but still unable to access the data. I must be missing something.

Appreciate your help.

Thank you,

Andrea

---

Andrea Kelley

Answer 1

Hello Andrea,

By the error message it seems you still don't have the permission to select data from that view.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Hi Olaf,

Thank you for your reply. I since learned that the server is windows authenticated but the db is sql authenticated. Power Pivot only requires one logon so I think the conflict lies there. When I login with sa credentials I am able to pull the data in.

Once I have Power Pivot for SharePoint installed will I still need to connect with sa? Will ens users be able to view the pivot tables with out those credentials?

Thank you again,

Andrea

---

Andrea Kelley

Answer 3

I since learned that the server is windows authenticated but the db is sql authenticated. Power Pivot only requires one logon so I think the conflict lies there. When I login with sa credentials I am able to pull the data in.

Hello Andrea,

No, the login mode is on server level, not on database level. And you should never use the SysAdmin account "sa" for any application access. It has fully access to SQL Server and it's to dangerous that someone get's the password for it.

If you logon from PowerPivot with your windows credentials, then you have to give that login the required permissions to query the view.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing