Get active user from Active Directory in Reporing Services 2008

Question

Get active user from Active Directory in Reporing Services 2008

htilburgs

Ten Centuries

Points: 1117
More actions
June 8, 2009 at 10:00 am

#384730

Ok, use Builtin Field "User ID" you would say. But that's to simple.
Let me explain why.
I use a dataset with the next in it:
declare @P_Gebruikervarchar(255)
set @P_Gebruiker= ''
set @P_Gebruiker= (select system_user)
When I run this in SQL Server Management Studio, I get the CORRECT result, example: "IT-DOMAIN\TILBXXXX"
When I run my report I get the name of the user defined in the DataSource Credentials.
We use a fixed username & password in the DataSource and not the current AD user, example "DatabaseReader"
Can someone help me to get the correct AD username of the current logged in who runs the report??

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

Not sure if it's the same in 08, but in SSRS 05 you can use the UserID from the Globals collection as an expression in pretty much any object.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

htilburgs Ten Centuries Points: 1117 More actions · Answer 2

Thanks for the reply, but as you could read in my first sentence is that not the solution.

Looking further for a solution, any help is welcome.....

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

ok, since you need to use it in a dataset (which I'm assuming because your sample code only seemed to get the userID for presentation or some such, which you can do with UserID...), create a hidden parameter default it to UserID and pass that parameter into your dataset as you would a parameter from any other parameter prompt. You can't get that info purely from SQL because the SQL Server doesn't know anything about your user, it only knows the credentials of the connection which you stated has been set to a static user.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

htilburgs Ten Centuries Points: 1117 More actions · Answer 4

(Luke, it seems that you where right afterall......)

Still looking and triggered why I couldn't get User!UserID to work for me, I searched and tryed on.

I've found the solution for my problem:

Define a Parameter (example @P_Username) and set de available value to NONE and default value to "=User!UserID".

When you are using more parameters, make sure this parameter is BEFORE the others.

Otherwise you get the error:

The report parameter ‘P_ParameterName’ has a DefaultValue or a ValidValue that depends on the report parameter “P_Username”. Forward dependencies are not valid.

Now you can use you're parameter @P_Username in a dataset/query.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

Glad you got it sorted.

Cheers,

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]