Getting Windows USERID in SSRS report

Question

Hi,

 

I'd like to keep track the my reports users. I have stored procedure and I am passing userid to it. It work in development server but in production server it always  gets Production server's generic userid.

The users connect to reporting server by windows authentication. I use  below code to get the user id and pass to the stored procedure.

 

DECLARE @userid varchar(8)
SET              @userid = UPPER ((SELECT     RIGHT(ORIGINAL_LOGIN(), Len(ORIGINAL_LOGIN()) - charindex('\', ORIGINAL_LOGIN()))))

 

is there way to get windows user id in ssrs report?

 

Thanks

Burak

Answer 1

Hi,

you get the windows user id in SSRS with the build in field - User!UserID

Do you know that at the SSRS reporting database is a view that returns statistic information about all executed reports, it also shows the user id.

Please let me know if that was helpful

wmel

Answer 2

Hi,

 

I don't have much experience with SSRS. Can you please explain it more. If you have an example it would be better.

Thanks

Burak

Answer 3

Hi,

The built in fields like User!UserID are available from any formula window - report builder or visual studion.

i.e. If you would like to pass the user id to your stored procedure, the stored procedure has to have a parameter like userid.

As soon as you connect the report to your stored procedure the report should have an parameter @userid at the parameter folder. 

Click the property of the parameter - click default values - specific values - click add button - click the formula button - at the category you will see the built in fields.

regarding log information:

open the SQL server management studio - connect to the SSRS database - at the database folder you find the report database - at views you should see the executionlog view,

This view shows you all executed reports, size, duration, export format, userid ....

wmel

Answer 4

Hi,

I have created parameter @userid then Click the property of the parameter - click default values - specific values - click add button then i chose built-in field then chose =User!UserID then hit the OK button it shows [&UserID]  as a value then I hit the Ok again. When I try to run it  I got below message :

 

The value expression for the query parameter '@userid' refers to a non-existing report parameter 'userid'  

Answer 5

Hi,

did you check if the parameter got created at you data set too?

right mouse click data set - data set properties - parameters, there you should have a parameter name=@userid, parameter value=[@userid],

if not add it.

you also have to make sure that the parameter name at you stored procedure is the same.

wmel

Answer 6

Hi Wmel,

 

It works!.

Thank you very much.

Burak

Answer 7

Hi Burak,

Please mark as answer if helpful

Thank's

wmel

Answer 8

Hi Wmel,

Do you know where is built in userID cached?

Answer 9

Hi Wmel,

Do you know where is built in userID cached?

It's stored in ReportServer.dbo.Users table (UserName column).

In the case of Windows Authentication it will be in the form of Domain\LoginID