ServiceDesk

Hi,

I'm currently trying to create a report that shows all tickets assigned to the logged in user, all the tickets assigned to any groups they belong to and all tickets assigned to all other members of the groups they belong to.

This is all working except for the fact I have to manually provide the UserID for the current logged in user as I cannot find a way to provide the variable to SQL. It has to be passing it to that page somewhere because this obviously works with the report builder.

Has anyone else had any luck with this?

Rian
www.onemansaltirisbattle.com

Anyone at all?

There are some canned T-SQL functions that return the logged-in user (like SYSTEM_USER) but they return the user in the context of SQL Server (not necessarily the Windows user)

This might be worth a try... put this in the beginning of your SQL:

DECLARE @User varchar(50)
select @User = nt_username from master..sysprocesses where spid = @@spid

@User might be what you're looking for, though it'll probably return the user in the context of the logged-in user of the SQL Server.

Thanks for the reply.

Unfortunately it does just send the username of the windows account that logs into SQL and not the one of the user currently running the report.

I'm really trying to think outside the box at the moment to get around this but keep hitting walls.

Rian

Without looking at the report builder code, I'm assuming it's just grabbing User.Identity.Name from the code-behind and passing that as a parameter to the SQL.

Yes I believe that is the case aswell however I cannot pass the parameter within the SQL query box as it will convert it to a string prior to running the query.

I'm trying to escape the string with something like below but without being able to view the pre compiled code around the SQL query I'm doing it all by feel.

Select [PrimaryEmail],[ADLoginName] from [User]
WHERE PrimaryEmail = '" & <%= User.Identity.Name %> & "';"
 

The query validation isn't helping either as it trys to prevent code it feels isn't part of a valid sql query and I'm trying to escape the query and tack my code on the end of it, SQL injection styles.