Products

Solutions

Learn More

Partners

Community

Blog

About

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

HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...SQL Query - Select users based on roleSQL Query - Select users based on role
Previous
 
Next
New Post
12/8/2016 11:05 AM
 

I have a need to select groups of users via a sql query based on their role in the system. I need to select by security role in the near term, and see the need upcoming for selecting by social role. Has someone done that? Is there a sample query you would be willing to share? Many thanks in advance! 

 
New Post
12/8/2016 2:47 PM
 
SELECT * FROM {databaseOwner}[{objectQualifier}Users]
WHERE UserID IN (SELECT UserID FROM {databaseOwner}[{objectQualifier}vw_UserRoles] WHERE PortalID = 0 AND RoleName = N'Administrators')

Cheers from Germany,
Sebastian Leupold (Microsoft MVP)

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
New Post
12/8/2016 4:29 PM
 

Many thanks for your reply!

The next related question is...How do I then cherry pick certain user profile entries (like cell and sales territory, etc.) either by property name or ID, and have it displayed as one row? I've been able to do it (incorrectly) whereby it creates 20+ rows for one user, and for each row lists a separate profile entry - not what I want. Again, many thanks for the assistance!

 
New Post
12/8/2016 10:30 PM
 

Thanks again for the help! Surprisingly I was able to figure it out. :) 

I combined a query that selected security roles with a forum query to select profile properties - here's the forum entry for select profile properties:
 http://www.dnnsoftware.com/forums/threadid/435402/scope/posts/sql-query-for-user-profile 

(Thank you Josh Wheeler and Sebastian Leupold!)

and, guess what -- it worked! I'm sure it can be made more efficient, for sure (I'd love for you SQL gurus to modify!), but I wanted to share this for those like me struggling with this issue.

Query is below, and thanks again!

Select

  U.UserID,

  U.Username,

  U.DisplayName,

  U.Email,

  Case UP.IsDeleted When 1 Then 'Yes' Else 'No' End As IsDeleted,

  (Select

    UserProfile.PropertyValue

  From

    UserProfile

  Where

    UserProfile.UserID = U.UserID And

    UserProfile.PropertyDefinitionID = (Select

      ProfilePropertyDefinition.PropertyDefinitionID

    From

      ProfilePropertyDefinition

    Where

      ProfilePropertyDefinition.PropertyName = 'Company' And

      ProfilePropertyDefinition.PortalID = 0)) As Company,

  (Select

    UserProfile_2.PropertyValue

  From

    UserProfile As UserProfile_2

  Where

    UserProfile_2.UserID = U.UserID And

    UserProfile_2.PropertyDefinitionID = (Select

      ProfilePropertyDefinition_2.PropertyDefinitionID

    From

      ProfilePropertyDefinition As ProfilePropertyDefinition_2

    Where

      ProfilePropertyDefinition_2.PropertyName = 'City' And

      ProfilePropertyDefinition_2.PortalID = 0)) As City,

  (Select

    UserProfile_1.PropertyValue

  From

    UserProfile As UserProfile_1

  Where

    UserProfile_1.UserID = U.UserID And

    UserProfile_1.PropertyDefinitionID = (Select

      ProfilePropertyDefinition_1.PropertyDefinitionID

    From

      ProfilePropertyDefinition As ProfilePropertyDefinition_1

    Where

      ProfilePropertyDefinition_1.PropertyName = 'State' And

      ProfilePropertyDefinition_1.PortalID = 0)) As State,

  (Select

    UserProfile_1.PropertyValue

  From

    UserProfile As UserProfile_1

  Where

    UserProfile_1.UserID = U.UserID And

    UserProfile_1.PropertyDefinitionID = (Select

      ProfilePropertyDefinition_1.PropertyDefinitionID

    From

      ProfilePropertyDefinition As ProfilePropertyDefinition_1

    Where

      ProfilePropertyDefinition_1.PropertyName = 'SalesTerritory' And

      ProfilePropertyDefinition_1.PortalID = 0)) As Territory,

  (Select

    UserProfile_1.PropertyValue

  From

    UserProfile As UserProfile_1

  Where

    UserProfile_1.UserID = U.UserID And

    UserProfile_1.PropertyDefinitionID = (Select

      ProfilePropertyDefinition_1.PropertyDefinitionID

    From

      ProfilePropertyDefinition As ProfilePropertyDefinition_1

    Where

      ProfilePropertyDefinition_1.PropertyName = 'JobTitle' And

      ProfilePropertyDefinition_1.PortalID = 0)) As [Job Title],

  aspU.LastActivityDate,

  Roles.RoleName

From

  Users As U Left Outer Join

  UserPortals As UP

    On U.UserID = UP.UserId Left Outer Join

  aspnet_Users As aspU

    On aspU.UserName = U.Username Inner Join

  UserRoles As UR

    On UR.UserID = U.UserID Inner Join

  Roles

    On UR.RoleID = Roles.RoleID

Where

  UP.PortalId = 0 And

  Roles.RoleName = 'Subscribers'

 
Previous
 
Next
HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...SQL Query - Select users based on roleSQL Query - Select users based on role


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.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out