Will "the Mighty" Strohl

Add All DNN User Accounts to a Security Role

Here is another tip, that grew out of an implementation using the Active Social module suite.  Once I was ready to roll the module out on an existing site, I needed to add all of the existing users to the security role that Active Social uses to give certain permissions to end-users.  I know I could have created a new role and added all users to it, but I wanted to use the new role that Active Social created for me.  As far as I know, DotNetNuke® does not provide a way to do this through the existing admin modules.

Since all of the users and roles are kept in the database, I naturally decided to generate a T-SQL script to accomplish this task for me.  At first, I thought it was going to be a complicated query, but I soon found myself to be wrong.  Here is how I investigated this task to find it’s solution.

First, I began looking through the source code of DNN library project.  Normally, when you want to perform a utility function like this task, there’s a method in the appropriate controller to accomplish it.  From there, a console app might be able to quickly written to perform your task.  In my case, I was not able to find an appropriate method in the UserController, RoleController, or any of the other classes where I thought this might be.  However, I did find one that might be useful.  In the RoleController class, there’s a method called AddUserRole().

The AddUserRole() method accepts the PortalId, UserId, RoleId, and optionally the effective and expiration dates.  In turn, this method calls the underlying AddUserRole stored procedure in the database.  The purpose of this method and data call is to add a single user account to a security role.  That is nearly what I was looking for, but it was all I could find – and it would do just fine.  This method pointed me to something I could work with.

Since I only needed to do this one time, I decided to write a T-SQL script to take advantage of the existing DNN stored procedures.  Here is what I came up with.

   3: DECLARE @RoleName NVARCHAR(50)
   4: -- name of the role to add users to
   5: SET @RoleName = N'SocialGroup_Member'
   7: DECLARE @RoleId INT, @PortalId INT, @UserId INT
   9: SELECT @RoleId = r.[RoleID], @PortalId = r.[PortalID] 
  10: FROM [dbo].[Roles] r 
  11: WHERE r.[RoleName] = @RoleName
  14: SELECT u.[UserID] 
  15: FROM [dbo].[Users] u 
  16: LEFT OUTER JOIN [dbo].[UserPortals] up ON up.[UserId] = u.[UserID] 
  17: WHERE up.[Authorised] = 1 AND up.[PortalId] = @PortalId
  19: OPEN [cUser]
  21: FETCH NEXT FROM [cUser] INTO @UserId
  24: BEGIN
  25:     EXEC [dbo].[AddUserRole] @PortalId, @UserId, @RoleId
  27:     FETCH NEXT FROM [cUser] INTO @UserId
  28: END
  30: CLOSE [cUser]
  31: DEALLOCATE [cUser]

This may appear to be a complicate query to some of you.  Let’s walk through it…


This statement tells the script which database to execute the following code against.  You should change the database name to match the name of the database that where your DNN site lives.

SET @RoleName = N'SocialGroup_Member'

This line of code is one of the most important.  Change the name “SocialGroup_Member” to match whichever security role you intend to add everyone too.

SELECT @RoleId = r.[RoleID], @PortalId = r.[PortalID] 
FROM [dbo].[Roles] r 
WHERE r.[RoleName] = @RoleName

This code snippet uses the role name you entered to grab the ID number that represents that role.

SELECT u.[UserID] 
FROM [dbo].[Users] u 
LEFT OUTER JOIN [dbo].[UserPortals] up ON up.[UserId] = u.[UserID] 
WHERE up.[Authorised] = 1 AND up.[PortalId] = @PortalId

In order to accomplish this task, I used a cursor.  I would be guarded against using cursors in production, but for a one time utility function like this, why not?  Here, we set-up the cursor to grab a collection of the user accounts that are authorized, and in the same portal as the chose security role.  (For the uninitiated, a cursor is basically a method we use in T-SQL to loop through data records.)


After opening the cursor, we need to start the loop, so we have to grab the first result to make it happen.

    EXEC [dbo].[AddUserRole] @PortalId, @UserId, @RoleId
    FETCH NEXT FROM [cUser] INTO @UserId

Similar to how we used to iterate through pretty much all data in classic ASP, we now loop through the result set in the cursor.  For each result, we call the stored procedure that we found earlier.  After the procedure gets executed, we then get the next result, and the loop continues.

I hope this helps you – if nothing else, I hope it at least inspires you to fix your own business problem.

Technorati Tags: ,,,

blog comments powered by Disqus