Passing Network ID to sp. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Passing Network ID to sp.

Can anyone show me how to open a report from asp page, passing credential information such as network ID to the stored procedure that the report based on. The content of the report is based on user’s security level. Thanks in advance,

How is the datasource for the report configured ? If it has stored credentials (i.e. uses the same login to connect regardless of the user executing the report) then the only thing you can really do is pass the NT User through as parameter (e.g. pick it up from Request.ServerVariables("AUTH_USER")) however this may leave the way open to passing a different value if using URL access. e.g. <pre>&lt;% <br />vuser = Request.ServerVariables("AUTH_USER")<br />vurl = "http://localhost/ReportServer?%2fParam%2fuser" & _<br /> "&rs:Command=Render&rc<img src=’/community/emoticons/emotion-4.gif’ alt=’:p‘ />arameters=false&rs:format=HTML4.0&user=" & vuser<br /><br />Response.Redirect vurl<br />%&gt;</pre> Seeing the url for the report a user could then substitiute the value so not very secure. The best way would be to either prompt the user for credentials to connect to the datasource (assuming you have enabled SSL!) and use one of the builtin sql functions like suser_sname() to get the username within the stored procedure or to use Kerberos to delegate the users credentials across the Report Server to the SQL Server and specify Windows NT Integrated Security for the datasource but this is dependent on Active Direcory and client OS amongst other things and is never that easy to set up [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><br />HTH<br /><br />Jasper Smith
Jasper, Thanks for your help. I have figured out a way to get user ID in sp based on your suggestion. Without writing a single of code, I made the report ready in 45 minutes.
This is a really useful tool for creating online reporting service. Thanks again for your help, Bill