Report Server Roles - Gaining access to

Does anyone out there know how to programatically determine the Role(s) of
the Logged In User running a report?
Thanks,
Bill MellOn Sep 19, 12:44 pm, "Bill Mell" <Bi...@.netforcement.com> wrote:
> Does anyone out there know how to programatically determine the Role(s) of
> the Logged In User running a report?
> Thanks,
> Bill Mell
You can pass back a hidden parameter from the report set to this
expression:
=User!UserID.ToString
Then in the stored procedure/query that is sourcing the report, run
the following query to obtain the associated roles.
SELECT R.ROLENAME
FROM REPORTSERVER.DBO.USERS AS U
INNER JOIN REPORTSERVER.DBO.POLICYUSERROLE AS P ON U.USERID = P.USERID
INNER JOIN REPORTSERVER.DBO.ROLES AS R ON P.ROLEID = R.ROLEID
WHERE U.USERNAME = @.UserName;
In this query, the @.UserName gets its value from the hidden parameter.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||I am actually trying to get the information before the report actually runs.
I have a custom web page to gather parameters for the report.
It seems, that at the time the web page is called, the User!UserID is not
loaded (or if it is, it is empty)
It is in the web page that I need this info.
Do you know of a way to get that info in this scenario?
Thanks,
Bill Mell
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1190254670.163591.20770@.y42g2000hsy.googlegroups.com...
> On Sep 19, 12:44 pm, "Bill Mell" <Bi...@.netforcement.com> wrote:
> > Does anyone out there know how to programatically determine the Role(s)
of
> > the Logged In User running a report?
> >
> > Thanks,
> > Bill Mell
>
> You can pass back a hidden parameter from the report set to this
> expression:
> =User!UserID.ToString
> Then in the stored procedure/query that is sourcing the report, run
> the following query to obtain the associated roles.
> SELECT R.ROLENAME
> FROM REPORTSERVER.DBO.USERS AS U
> INNER JOIN REPORTSERVER.DBO.POLICYUSERROLE AS P ON U.USERID = P.USERID
> INNER JOIN REPORTSERVER.DBO.ROLES AS R ON P.ROLEID = R.ROLEID
> WHERE U.USERNAME = @.UserName;
> In this query, the @.UserName gets its value from the hidden parameter.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||We have created many User Aware Reports which pass the user id back to
the query which selects the data required.
It would seem the report should have this built in so no sensitive
data is called where as if you rely on the web page to pass this
parameter, there might be a security loop-hole.
You can pass other parameters from the web page which basically pass
these on to the parameters in the report.
If you run the report by itself and select the parameters you will see
them in the url.
The login/userid should be called there but rather as part of the
report itself as enrique has noted.
Regards,
Tom Bizannes
Microsoft Sql Server Specialist
http://www.macroview.com.au
Sydney, Australia