New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsReportsReportsSUSER_SNAME() used in Report Module doesnSUSER_SNAME() used in Report Module doesn't get NT ID -- Help!
Previous
 
Next
New Post
6/11/2008 3:28 PM
 
I am creating a report and uses SUSER_SNAME() function to get NT user ID. The purpose of the report is for intranet user to view their report. I tested in Visual Studio SQL environment and I get the correct Windows Authenticate user ID. Then I pasted the SQL syntax into Reports Module but I got “admin” ID. Anyone can solve the problem?
 
Thanks in advance
 
New Post
6/11/2008 6:51 PM
 

All DotNetNuke modules connect to the SQL Server using the same user (as configured in the web.config). If your web.config file specifies Integrated Security, it is the user under which ASP.Net is running (usually "NT AUTHORITY\NETWORK SERVICE"), not the current DNN user, that will be authenticated with SQL Server.


Andrew Nurse
DotNetNuke Core Team Member and Reports Module Project Lead
Microsoft Certified Professional Developer

 
New Post
6/12/2008 11:10 AM
 

Andrew Nurse wrote

All DotNetNuke modules connect to the SQL Server using the same user (as configured in the web.config). If your web.config file specifies Integrated Security, it is the user under which ASP.Net is running (usually "NT AUTHORITY\NETWORK SERVICE"), not the current DNN user, that will be authenticated with SQL Server.

Got it!  And thanks very much Andrew for your quick reply.
 
So, is any way in the Report Module I can get current user's Windows Authentication ID from SQL database or SQL syntax? I am using Authentication Providers Module. I am thinking how I can get current user ID from database? Any advice?
 
Many thanks.
 
New Post
6/12/2008 12:08 PM
 

You can access the current DNN user's ID by using the @UserID parameter in your SQL Query. I'm not exactly sure how the Windows Authentication Provider works, but I believe that the username in the Users table for that user is the same as their Windows user name, so the following query should work (or at least point you in the right direction).

SELECT Username FROM {oQ}Users WHERE UserID=@UserID


Andrew Nurse
DotNetNuke Core Team Member and Reports Module Project Lead
Microsoft Certified Professional Developer

 
New Post
6/12/2008 12:52 PM
 

Andrew Nurse wrote

You can access the current DNN user's ID by using the @UserID parameter in your SQL Query. I'm not exactly sure how the Windows Authentication Provider works, but I believe that the username in the Users table for that user is the same as their Windows user name, so the following query should work (or at least point you in the right direction).

SELECT Username FROM {oQ}Users WHERE UserID=@UserID

It worked!!
I am using Survey Module and would like to Create a report that can be viewed by users. Here is the SQL syntax that I would like to share with others. it maybe useful for someone else.

SELECT DISTINCT Users.DisplayName, Surveys.ViewOrder, Surveys.Question, SurveyOptions.OptionName
FROM         Surveys INNER JOIN
                      SurveyOptions ON Surveys.SurveyID = SurveyOptions.SurveyID INNER JOIN
                      SurveyResults ON SurveyOptions.SurveyOptionID = SurveyResults.SurveyOptionID INNER JOIN
                      Users ON SurveyResults.UserID = Users.UserID INNER JOIN
                      UserProfile ON Users.UserID = UserProfile.UserID
WHERE     (UserProfile.PropertyDefinitionID = 45) AND (NOT (Surveys.ViewOrder IN (1, 9))) AND (Users.UserID IN
                          (SELECT DISTINCT @UserID
                            FROM  Users ))

Many thanks again.

 
Previous
 
Next
HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsReportsReportsSUSER_SNAME() used in Report Module doesnSUSER_SNAME() used in Report Module doesn't get NT ID -- Help!


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.