This question comes up quite often. A report requires a user to be authenticated using

their own network user name and password. Perhaps the data source performs row-level

filtering for the user or the UserID global user object is used in a query or filter

expression to return user-specific data. Whatever the case may be, how can you send

scheduled subscriptions and filter the report for that user? This can be a challenge

when scheduling a subscription for the report because a subscription requires that

credentials be stored in the data source. This is not so much a limitation of Reporting

Services as it is a logistical challenge. Why? ?because a subscription is designed

to run unattended. The data source credentials must be stored so when the subscription

fires in the middle of the night, the report and database query can run without the

user logging in and sitting in front of their computer.

There are at least two possible solutions to this quandary . The first is to pass

the UserID as a parameter and then filter the data in the query. The data source will

still have to use a stored application credential but if user-specific filtering is

the goal, this technique should meet the requirement. You can store each user?s UserID,

along with any other report parameters and report execution settings (like the rendering

format, etc.) in a table and then broadcast the report out to several users with a

data-driven subscription.

The other approach is to use a scheduled subscription to email a link to the report,

which will then run on-demand when the user clicks the emailed link. To do this:

• Deploy the report you want the user to run on-demand without stored credentials in

  the data source.

• Create a very simple, small report containing only a textbox to use as a link. Set

  a URL action on this textbox to open the other report, using the full path to the

  report on the server.

• Schedule a subscription for this small report and use the option to embed it into

  the email.

When the user receives the subscription email, they will just see a link, which as

actually a report drill-through action that they will use to open the second report.

Since that report doesn't use stored credentials, it will use their own credentials

to authenticate.

Weblog by Paul Turley and SQL Server BI Blog.