How to automatically fetch team members AD IDs to SQL query

[Anonymous]

Hi Community,

I have a requirement which is related to SQL Query(Sql database as data source) . I need to pull data from database and connect to Power BI for designing a report. I designed the Query and in my Where Clause , i have some criteria to put (AD Ids) related to a team we are designing a report. ( Sample shown below ) 

Query :

select Incident_Number,Customer Name, LoginID from Tblname where LoginID in ('venks','gary','sara') 

Every time a new team member is added/removed ,we have to edit the query and make necessary changes to see new members related data  . They do not have a group name . so its getting difficult to manage this process. 

Is there a way to avoid manual hard coding of this members AD IDs and automate by using any method ?

Kindly assist !! 

Thanks,

G Venkatesh 

[pranit828]

HI @Anonymous 

I would use USERNAME() to pull it.

[Anonymous]

Thanks for your Quick reply. Could you please elaborate more on how to use this ? I didnt get the complete details . 

[v-zhenbw-msft]

Hi @Anonymous ,

Do you mean that if the Login ID changes, the SQL statement needs to be modified?

For example, we add a new member, his login ID is A, then the SQL will be changed like this,

select Incident_Number,Customer Name, LoginID from Tblname where LoginID in ('venks','gary','sara',”A”)

If yes, there are some ways you can refer.

1. Change the information in data source, not in Power BI Desktop.
2. Filter the Login ID in Power Query.
3. Or you can configure RLS, please refer the following articles.

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls

https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies

Maybe you can use PowerShell to manage AD roles, please refer this blog.

https://community.powerbi.com/t5/Community-Blog/Power-BI-Group-management-using-Active-Directory-roles-and/ba-p/308092

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi ,

Good Day !!

Yes exactly this is what i am looking for. Instead of we manually changing the SQL every time a new member is added or if existing team member leaves the team ,SQL should automatically respond to the changes. 

I have gone through the links mentioned below. However, everything speaks about managing roles etc in Power BI , but nothing on SQL Query . I do not know if i am missing something . But you got my point as how you have added new team member called A .could you please explain me further ? 

Thanks

G Venkatesh

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

Sorry for that there seems to be no automated method on Power BI Desktop.

We assume that your data source is SQL Server.

If your LoginID is changed, then update it in SQL Server.

Then you don’t need to configure the where statement, the LoginID already has “A”

select Incident_Number,Customer Name, LoginID from Tblname

Maybe you can refer the following article about inserting or delete data in SQL server.

https://www.pluralsight.com/guides/manipulating-data-using-insert-update-delete-sql-server

This seems to be the job of your company’s DBA.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.