Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Sam Duval]

Not really a power bi question, but hoping the collective Brain power here can help me.

I’m trying to add users of a department of my company call it “reporter” as a user on the azure sql Server. 

I’ve created a user that’s mapped to their ad group and mapped that to a database role that has permissions select, execute, insert in a schema called reporter.

I create a view in the server called reporter.sales which for simplicity is this code: 
Select * from dbo.master_sales

When someone from the reporter department trys to run a paginated report using their oauth, they are getting an error message that says they don’t have permission to read dbo.master sales.

I’ve done this before with other departments so I know it can be done, but I can’t figure out what I did differently with the others.

Thanks all

——————————
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
——————————

[Sean Graham]

Hi Sam,
     Yep, not really a Power BI question 🙂 
     But is it a case that you are only giving permission to the view and not the underlying objects that the view references like the dbo.master_sales?
     Also I think that select permission is all that is needed.

Thanks,

Sean

——————————
Sean Graham
BI Consultant
Dublin
——————————
——————————————-

[Riccardo Perico]

Is the underlying table in a different database?

——————————
Riccardo Perico
Data Platform & BI Specialist
SolidQ Global
——————————
——————————————-

[Sam Duval]

?

It’s the same database, an external table, but same database.

I’ll double check but the others I’ve setup don’t have select access to the underlying table directly. I’m trying to prevent them from writing a swl script to query underlying tables. It’s a rudamentry security check. That they only have access to the data exposed in their schemas, they can’t back door their way to the whole database. We got dinged for that in our old system during an audit.

——————————
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
——————————
——————————————-

[Sam Duval]

Closing the loop here in case someone stumbles upon this post. The issues was the schema owner has to be the same schema as the underlying table. so in order to query dbo.tablex from sales.tablex_view schema sales has to be owned by dbo

——————————
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
——————————
——————————————-