HowTo return User!UserID using Oracle sys_context('USERENV','OS_US

Our SSRS instance is hosted in infrastructure that does not have Active 
Directory.

The SSRS has a custom security extension and the process is running under 
the Network Service account. The custom security extension populates the 
'User!UserID' runtime variable within SSRS.

I have Oracle colleagues who want to know if there is a way that their 
sys_context('USERENV', 'OS_USER') calls can be populated with the User!UserID 
variable.

This is my response:

I am virtually certain that there is no practical way that SSRS can 
substitute the 'OS_USER' value within the Oracle driver, because as I 
understand it the SSRS process would need to make a very low-level call 
within the OS stack to a DHS customised variant of the Oracle driver.

This issue has been raised a number of times, and my understanding is that 
what is trying to be achieved here is to avoid having to re-implement a suite 
of SSRS report queries to dynamically employ the SSRS 'User!UserID' value, 
where the queries for the reports are currently employing the 
sys_context('USERENV', 'OS_USER') call.

To help understand what is going on, this is my understanding.

Because the CRT infrastructure is EAZ hosted there is no Integrated Windows 
Authentication, and so the CRT processes are run Anonymously under the 
Network Service account.

My understanding is that the sys_context('USERENV', 'OS_USER') call is being 
populated not by the SSRS process, but by the Oracle driver reading the SSRS 
process at the OS level.

Where the query for a report is developed using a client tool on HSNet 
(where there is Integrated Windows Authentication) and employs the 
sys_context('USERENV', 'OS_USER') call, the query cannot be implemented by 
simply copying and pasting the P/SQL into the report's dataset definition. 
The query will need to be reimplemented in a parameterised form, where the 
parameter is populated using the SSRS's User!UserID runtime variable. 

Reports that employ the sys_context('USERENV', 'OS_USER') call will run in 
the HSNet-hosted CRT Dev environment just fine and return the expected value 
(equivalent to the User!UserID variable), however while they are functionally 
equivalent values when run within HSNet the underlying mechanism of their 
population is entirely different.

When a report such as the above gets deployed to CRT staging or Production 
the only way to get the user and use it within the report query is to 
parameterise the query and populate the parameter with the User!UserID SSRS 
runtime variable.

Could someone correct me or verify this?

Thanks, Alex
0
Utf
4/23/2010 4:50:01 AM
sqlserver.reportingsvcs 542 articles. 0 followers. Follow

1 Replies
813 Views

Similar Articles

[PageSpeed] 17

I think you have a strong understanding of the issue. I do not see any holes 
in your description of what is happening. You have User!UserID. No way 
around it, the queries will need to use that (or it needs to be passed to 
the stored procedure).

-- 
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Alex Thomas" <AlexThomas@discussions.microsoft.com> wrote in message 
news:4C68730E-7ED8-4AD6-83F9-658C2902A994@microsoft.com...
> Our SSRS instance is hosted in infrastructure that does not have Active
> Directory.
>
> The SSRS has a custom security extension and the process is running under
> the Network Service account. The custom security extension populates the
> 'User!UserID' runtime variable within SSRS.
>
> I have Oracle colleagues who want to know if there is a way that their
> sys_context('USERENV', 'OS_USER') calls can be populated with the 
> User!UserID
> variable.
>
> This is my response:
>
> I am virtually certain that there is no practical way that SSRS can
> substitute the 'OS_USER' value within the Oracle driver, because as I
> understand it the SSRS process would need to make a very low-level call
> within the OS stack to a DHS customised variant of the Oracle driver.
>
> This issue has been raised a number of times, and my understanding is that
> what is trying to be achieved here is to avoid having to re-implement a 
> suite
> of SSRS report queries to dynamically employ the SSRS 'User!UserID' value,
> where the queries for the reports are currently employing the
> sys_context('USERENV', 'OS_USER') call.
>
> To help understand what is going on, this is my understanding.
>
> Because the CRT infrastructure is EAZ hosted there is no Integrated 
> Windows
> Authentication, and so the CRT processes are run Anonymously under the
> Network Service account.
>
> My understanding is that the sys_context('USERENV', 'OS_USER') call is 
> being
> populated not by the SSRS process, but by the Oracle driver reading the 
> SSRS
> process at the OS level.
>
> Where the query for a report is developed using a client tool on HSNet
> (where there is Integrated Windows Authentication) and employs the
> sys_context('USERENV', 'OS_USER') call, the query cannot be implemented by
> simply copying and pasting the P/SQL into the report's dataset definition.
> The query will need to be reimplemented in a parameterised form, where the
> parameter is populated using the SSRS's User!UserID runtime variable.
>
> Reports that employ the sys_context('USERENV', 'OS_USER') call will run in
> the HSNet-hosted CRT Dev environment just fine and return the expected 
> value
> (equivalent to the User!UserID variable), however while they are 
> functionally
> equivalent values when run within HSNet the underlying mechanism of their
> population is entirely different.
>
> When a report such as the above gets deployed to CRT staging or Production
> the only way to get the user and use it within the report query is to
> parameterise the query and populate the parameter with the User!UserID 
> SSRS
> runtime variable.
>
> Could someone correct me or verify this?
>
> Thanks, Alex 

0
Bruce
4/23/2010 5:05:21 PM
Reply:

Similar Artilces:

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

Using scanner in Word97
I want to scan a picture into word97 using my HP 4370 ScanJet. Could not find option of "From Scanner" under "Insert" --> "Picture". Apparently I must need some sort of Word97 Add-On. What and where is the add-on? Is it on the Office97 CD? Same applys to Excel97. Also, does microsoft sponsor a Word97 / Office97 discussion group? If so, would appreciate a link. "PSRumbagh" <PSRumbagh@discussions.microsoft.com> said this in news item news:39100DC1-A7EE-4679-881D-526BAA386620@microsoft.com... > I want to scan a picture ...

Return Receipt
Hi - I received a message with a "return Receipt" and said yes not remebering that I was not at my home computer. My ISP does not allow remote sending - so now everytime I hit send/receive it tries to send the receipt. There is nothing in the outbox or send box. Any ideas on how to get rid of this? Thanks ...

Office 2007 forms
I am creating a form with office 2007, will those people who do not use office 2007 be able to fill in my form? should I save it in a particular format? thanks Provided you start from the normal template, don't use fonts that were introduced with Word 2007, and save the form in Word 97-2003 document format, anyone with Word 97 or later should be able to open it. Use only the legacy form fields, to which end http://gregmaxey.mvps.org/Classic%20Form%20Controls.htm will make things easier. -- <>>< ><<> ><<> <>>< ><<...

Using later version of microsoft access
Hi, I've got access 2000 on my computer. When I go to open a database someone sent me I get an error message : this database is in an unrecognized format. The database may have been created with a later version of microsoft access. Is there any way I can open and use this file (short of upgrading to later version of access)? If you do not have Access 2002 or 2003, ask the person to save it in Access 2000 format for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

users with restricted access
We have some users that we have directed to only get their information from a report that has been set up. Because of that, I set up a parameter query to make the information more easy to see. The parameter query prompts for last name or broker #, is there a way, when the last name is entered to include Jr's & Sr's? Or should this be another field in the table to make the last name field more accurate? ...

SBS 2003 moving of users files
I run SBS 2003 and due to the amount of data on the users drive it has become chokers and have installed a new 1tb drive to keep up with demand for space. I need to move all the data to the new drive but unsure of the process. Is there an easy way of doing this? As it needs to be done asap Thanks -- JimmyJames ------------------------------------------------------------------------ JimmyJames's Profile: http://forums.techarena.in/members/255792.htm View this thread: http://forums.techarena.in/small-business-server/1357051.htm http://forums.techarena.in You c...

User unknown
I made a new user on the exchange server, but if i want to make the account in outlook, Outlook says: "the name could not be matched to a name in the adresslist" I don't know what to do anymore. All the rights are the same as my collegea. can somebody help me? Go back to AD where you created the account, preferably on the Exchange Server. Is there an email address listed? If there isn't then right click on the Recipient Update Service in ESM and click update now. Advise what happens then... >-----Original Message----- >I made a new user on the exchange server, b...

Using Company Wide Mail Templates.
Hi, I do not know if i am at the right spot here, or if it is evne possible, but i got the following question. My boss would like me to make sure that every outgoing mail has the same looks. It starts by adding a signature that is the same for everyone, except with ofcourse personalized information. This was easily done by giving everyone a signature. The next question is however, to put the head of our website, also above our mail. This means that every user that sends a mail, the mail will have a nice header, underneath that header, the mail is typed, and then its ended with the si...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

Can't re-enter a previously deleted User ID
We changed the spelling of a User ID (applewicks to appelwicks) and then deleted it (since he couldn't remember his password and the button for password was greyed out so we couldn't change it.) And now we can't re-enter the same user ID even though it doesn't appear in the window any longer. Here is the error we get: ODBC SQL server driver: The log in appelwicks already exists. Thanks! I believe you have to delete the old ID through Enterprise Manager as well. "cliffs" wrote: > We changed the spelling of a User ID (applewicks to appelwicks) and then ...

Using Publisher 200 with Publisher 2003
How do I covert PUB2000 documents to Pub2003 documents and vice versa? Pub 2003 can open anything, no conversion necessary. Going backward is a bit trickier. File - Save As and chance the file type to a Pub 2000 file. Possible problems can arise if you've used a feature that was not available in the 2000 version and your file size will grew immensely. -- JoAnn Paules MVP Microsoft [Publisher] "nasuco" <nasuco@discussions.microsoft.com> wrote in message news:500C7A7A-4026-434C-8CC2-2DFDB69D81C4@microsoft.com... > How do I covert PUB2000 documents to Pub2003 do...

Default User object security
Windows 2003 SP2 I am trying to fix a problem where delegation of control is not working properly to usr OUs. I ca't seem to keep permissions on user account objects that allow user accounts to be moved between OUs. I think it may be related to protected account membership on the user objects themselves. ALso the "inherit permissions from parent" is unchecked on user objects. Certain user new user objects work fine and are inheriting. What are the default security to use on user objects so that i can remvoe membership from protected groups and how should I a...

how to know how many e-mail each user receives per day?
We are running Exchange 2003 on Windows 2003. I would like to know how to get an average of how many e-mails each users send and receives per day. ...

calendar for multiple time zone users: all day events
Users in different time zones post absences and meetings to a shared company calendar. When an all day event is scheduled in one time zone, it shows as spanning two days for other time zone users. How do we make this work properly? For the others it does intrude on a second day. BossLady wrote: > Users in different time zones post absences and meetings to a shared company > calendar. When an all day event is scheduled in one time zone, it shows as > spanning two days for other time zone users. How do we make this work > properly? Until you upgrade to Outlook...

PO Return for uninvoiced item
We're trying to process a return against a PO line that was received in error. The receipt has not been matched to an invoice, and won't be since it was our mistake to receive it in the first place. There has been no further inventory or sales activity on the item since the PO receipt. When I try to enter a 'Return', the specific receipt transaction doesn't show. If I enter 'Return with Credit' then it does show. Since we won't be invoiced I'd rather not create a credit on the vendor account, but there doesn't appear to be an option at this poi...

Can a Component written in VBScript return an array of Objects?
Can a Component written in VBScript return an array of different Object types? In other words the elements of the array are not the same type. -- 100% Accurate Display Screen OCR http://www.OCR4Screen.com Peter Olcott schrieb: > Can a Component written in VBScript return an array of different Object > types? In other words the elements of the array are not the same type. > Yes; all data in VBScript is stored in Variants, so you may even mix plain and oop 'objects'. The retured thingy is just another Variant of subtype array. So just create/fill your array an...

How to use different return email addresses
I use outlook 98. I have a mailbox with three aliases. People sending me emails can use any of the three email aliases and the email will arrive in the same mailbox. Accessing the mailbox once will retrieve all messages regardless of which alias was used. If I reply to these emails though, I want the recipient to think that the email has come from the alias that they originally used. What outlook always seems to do is use the email address of the service that is listed first in Tools->Services->Delivery. Is there any way Outlook can be set up so when I reply to a given email, my email ...

User not visible in Global Address List
I have one user who from some reason does not show under the Global Address List. They do, however, show under all users and can succesfully send\receive email. All Exchange functionality works as designed. When I go into Exchange Administration, and view the results of the query for the Global Address List, the user shows. This issue is effecting all Exchange 2003 \Outlook 2003 Users. The user is not visible to anyone under Global Address List. I have also check on the properties of the user, and the box to "Hide from Address List" is not checked. Any assistance would ...

Save as CSV isn't saving comma delimited, but is using semi-colon.
Save as CSV isn't saving comma delimited, but is using semi-colon... How do I get it to save using commas only (,)? (I'm using semicolons for other delimitations and so can't just do a global replace.) Thanks so much for your help! Hi Excel uses your Windows regional settings for saving CSV files. Also ho should an importing program of this CSV file know whether it is the list separator or one of your other separators? -- Regards Frank Kabel Frankfurt, Germany CCinItly wrote: > Save as CSV isn't saving comma delimited, but is using semi-colon... > How do I get it ...

One table with two date fields returning records in a date range
I have a table that has several columns, two of which are date fields. A start_date and comp_date for start and completion date. I need to run a query that will return all the records between two dates selected by a user. Not hard, piece of cake. The problem I am having is that my query isn't returning all the records that overlap a given date range. For example: I select a date range: June 1 to September 1. My query will return 10 records that start and stop between June 1 and September 1. This is good and correct, but.... ...I have several records that start before June ...

easy to use templates
Hi We have outlook templates that people fill in each morning and send to a distribution list. But they are not that easy to fill out as you have to place the curser in the proper spot to type then do that throughout the entire document. It is easy to overlook items. Is there a way to set up a template that you can just hit tab or enter and it automatically goes to the next area that you need to type information? -- Thank-you! Ruth What exactly are you using now? In other words, what do you mean by "templates"? Also, do you meet the prerequisites for using...

Using ACT! with Outlook
I want to use ACT! with Outlook 2002 to track my emails. The Outlook Help contents say to go to Tools, Options, and there will be a tab for ACT! to click to attach emails to contacts. The ACT! tab does not show up. Is there a way to get the ACT! tab to show? Thanks for any help! Check Help > About Microsoft Outlook > Disabled items. If the add-in for ACT is listed there, re-enable it. -- TechieBird http://bwain-dump.blogspot.com "Jon K" wrote: > I want to use ACT! with Outlook 2002 to track my emails. The Outlook Help > contents say to go to Tools, Optio...

use pst file
I have a backup pst file that I can not get into Outlook 2007. Message says This Personal Folders File is already in use with this profile. The pst file is goog as of a cuple days ago. How do I get it into Outlook 2007? How are you trying to "get it into" Outlook? File > Open > PST usually works. "topcat" <topcat@discussions.microsoft.com> wrote in message news:0D1CD035-564D-49C0-921D-E9223BE582A6@microsoft.com... > I have a backup pst file that I can not get into Outlook 2007. Message says > This Personal Folders File is already in use...