How to Retrieve Logged in USERNAME in POWER BI Reports. A Perfect Alternate Way for Direct Queries

Introduction

So many times, I have asked Mr. Google to give me a solution to pass user name as a parameter to POWERBI report for various development purposes. Google gives a lot of details but unfortunately, they do not suit my requirements. However, I would like to thank all people who contribute their ideas and implementations on all the forums in Google.

I hope my way is the perfect alternative way to pass username as a parameter and we can even get it as a dataset. To implement this method, we require Analysis services to run in TABULAR Mode because POWERBI supports Tabular model.

First, create a stored procedure of your logic where you want to pass the user name as a parameter. Let us hope this stored procedure is “usp_StoredProc1” and parameter name is “@UserName”.

create procedure usp_StoredProc1--Your Storedproc name
(
@UserName nvarchar(100)
/*
Add Other parameters
*/
)
AS
BEGIN
/*
Your Logic Here
*/
END

Now create a stored procedure to execute the first procedure in master database of SQL Server to get logged-in username. Sample username execution is as below:

create procedure usp_StoredProc2
AS
BEGIN
Declare @UName varchar(15) = NULL

Select @UName = SYSTEM_USER

–Execute AS USER = ‘your desired username’ — use this if you want 
                                           — to execute stored proc with other user

Execute <>.<>.usp_StoredProc1 @UserName = @UName

–REVERT –If you are using Execute as User you need to use this

END

In PowerBI Report, select data source SQL Server and write a command with the below syntax:

select *
from
openrowset('SQLOLEDB','SERVER=Server Name;Trusted_Connection=yes;',' _
            set fmtonly off;exec master.dbo.Stored Proc Name')

The above query will give you results filtered with User Name.

Problematic Situations

When you install only SSAS with named instance, this method may not work.

Caution

You need to have public access for all the accessing users to SQL Server.

History

• 2^nd July, 2019: Initial version