Try our conversational search powered by Generative AI!

Jonas Lindau
May 31, 2013
  3113
(1 votes)

Users disappear from club’s access groups when using AD and Relate–a workaround…

I’ve seen a few forum posts from users having this issue. One of the best threads covering this subject is this one: http://world.episerver.com/Modules/Forum/Pages/thread.aspx?id=63028

This thread actually contains a good way to reproduce the error and also two different solutions that might work. The first solution is to specify the groups to transfer instead of using *. This worked for me at first, but suddenly started to act weird.

But since I wanted to know more about this, I started with running SQL Profiler to see who, and when, accessed the database table tblEPiServerCommonGroupUser. It turned out that what’s happening is that all rows for the current user gets deleted when the new session starts. After that, the users group belongings gets inserted back to the table, BUT it seems like it never inserts non-AD groups, like “Group for club with id: xx”. This is what happens:

 

   1: DELETE FROM tblEPiServerCommonGroupUser WHERE intUserID = @intUserID 
   2: INSERT INTO tblEPiServerCommonGroupUser (intGroupID, intUserID) VALUES (@intGroupID, @intUserID) 
   3: INSERT INTO tblEPiServerCommonGroupUser (intGroupID, intUserID) VALUES (@intGroupID, @intUserID) 
   4: INSERT INTO tblEPiServerCommonGroupUser (intGroupID, intUserID) VALUES (@intGroupID, @intUserID) 
   5: INSERT INTO tblEPiServerCommonGroupUser (intGroupID, intUserID) VALUES (@intGroupID, @intUserID) 
   6: INSERT INTO tblEPiServerCommonGroupUser (intGroupID, intUserID) VALUES (@intGroupID, @intUserID) 
   7: INSERT INTO tblEPiServerCommonGroupUser (intGroupID, intUserID) VALUES (@intGroupID, @intUserID) 
   8: INSERT INTO tblEPiServerCommonGroupUser (intGroupID, intUserID) VALUES (@intGroupID, @intUserID) 
   9: INSERT INTO tblEPiServerCommonGroupUser (intGroupID, intUserID) VALUES (@intGroupID, @intUserID) 

 

It seems that who’s responsible for this is EPiServer.Common.Web.Authorization.Integrator and it may be possible to do something about this behavior. For now, I have no solution. Instead I tried to find a way to insert the correct groups back to the table. This stored procedure restores the table with correct information for the current user. It could be executed in Session_Start to make sure the user belongs to the correct groups the current session:

 

   1: SET ANSI_NULLS ON 
   2: GO
   3:  
   4: SET QUOTED_IDENTIFIER ON 
   5: GO
   6:  
   7: -- ============================================= 
   8: -- Author:        Jonas Lindau, MSB 
   9: -- Create date: 2013-05-30 
  10: -- ============================================= 
  11: CREATE PROCEDURE [dbo].[spCustomEpiServerFixGroups] 
  12:     @intUSerId int 
  13: AS 
  14: BEGIN 
  15:     SET NOCOUNT ON;
  16:  
  17:     DECLARE cClubs CURSOR 
  18:     FOR 
  19:         SELECT intClubID FROM tblEPiServerCommunityClubMember where intUserID = @intUserId 
  20:         
  21:     OPEN cClubs
  22:  
  23:     DECLARE @intClubId int 
  24:     DECLARE @intGroupId int
  25:  
  26:     FETCH NEXT FROM cClubs INTO @intClubId 
  27:     WHILE @@FETCH_STATUS <> -1 
  28:     BEGIN 
  29:         SET @intGroupId = (SELECT intID FROM tblEPiServerCommonGroup WHERE strName = 'Group for Club with Id: ' + CAST(@intClubId as varchar))
  30:  
  31:         IF NOT EXISTS(SELECT * FROM tblEPiServerCommonGroupUser WITH(UPDLOCK, ROWLOCK, HOLDLOCK) WHERE intGroupID=@intGroupId AND intUserID=@intUserId) 
  32:         BEGIN 
  33:             --PRINT 'Values inserted for user: ' + CAST(@intUserId AS VARCHAR) + ' and group: ' + CAST(@intGroupId AS VARCHAR) 
  34:             INSERT INTO tblEPiServerCommonGroupUser(intGroupID, intUserID) VALUES(@intGroupId, @intUserId) 
  35:         END 
  36:         /* 
  37:         ELSE 
  38:         BEGIN 
  39:             PRINT 'Values already exists for user: ' + CAST(@intUserId AS VARCHAR) + ' and group: ' + CAST(@intGroupId AS VARCHAR)            
  40:         END 
  41:         */ 
  42:         FETCH NEXT FROM cClubs INTO @intClubId 
  43:     END 
  44:     CLOSE cClubs 
  45:     DEALLOCATE cClubs 
  46: END
  47:  
  48: GO

 

The procedure above can be executed like this:

   1: protected void Session_Start(Object sender, EventArgs e) 
   2: { 
   3:     try 
   4:     { 
   5:         DatabaseHandler.Instance.ExecuteNonQuery("spCustomEpiServerFixGroups", CommunitySystem.CurrentContext.DefaultSecurity.CurrentUser.ID); 
   6:     } 
   7:     catch { } 
   8: }

 

If you need to correct the entire table in one job, you could try the script below.

What it does is extract a list of all users that is a member of any club and then insert the correct GroupId and UserId into tblEPiServerCommonGroupUser. I  have tried this for a couple of days and it seems to work, but be warned! It may do stuff it not intended to, so use at own risk!

This is a one-time fix.

 

   1: SET NOCOUNT ON;
   2:  
   3: DECLARE cClubMembers CURSOR 
   4: FOR 
   5:     SELECT DISTINCT intUserID FROM tblEPiServerCommunityClubMember
   6:  
   7: OPEN cClubMembers
   8:  
   9: DECLARE @intUserId int
  10:  
  11: FETCH NEXT FROM cClubMembers INTO @intUserId 
  12: WHILE @@FETCH_STATUS <> -1 
  13: BEGIN 
  14:     DECLARE cClubs CURSOR 
  15:     FOR 
  16:         SELECT intClubID FROM tblEPiServerCommunityClubMember where intUserID = @intUserId 
  17:         
  18:     OPEN cClubs
  19:  
  20:     DECLARE @intClubId int 
  21:     DECLARE @intGroupId int
  22:  
  23:     FETCH NEXT FROM cClubs INTO @intClubId 
  24:     WHILE @@FETCH_STATUS <> -1 
  25:     BEGIN 
  26:         SET @intGroupId = (SELECT intID FROM tblEPiServerCommonGroup WHERE strName = 'Group for Club with Id: ' + CAST(@intClubId as varchar))
  27:  
  28:         IF NOT EXISTS(SELECT * FROM tblEPiServerCommonGroupUser WITH(UPDLOCK, ROWLOCK, HOLDLOCK) WHERE intGroupID=@intGroupId AND intUserID=@intUserId) 
  29:         BEGIN 
  30:             --PRINT 'Values inserted for user: ' + CAST(@intUserId AS VARCHAR) + ' and group: ' + CAST(@intGroupId AS VARCHAR) 
  31:             INSERT INTO tblEPiServerCommonGroupUser(intGroupID, intUserID) VALUES(@intGroupId, @intUserId) 
  32:         END 
  33:         /* 
  34:         ELSE 
  35:         BEGIN 
  36:             PRINT 'Values already exists for user: ' + CAST(@intUserId AS VARCHAR) + ' and group: ' + CAST(@intGroupId AS VARCHAR)            
  37:         END 
  38:         */ 
  39:         FETCH NEXT FROM cClubs INTO @intClubId 
  40:     END 
  41:     CLOSE cClubs 
  42:     DEALLOCATE cClubs
  43:  
  44:     FETCH NEXT FROM cClubMembers INTO @intUserId 
  45: END
  46:  
  47: CLOSE cClubMembers 
  48: DEALLOCATE cClubMembers 
  49:  

 

If you know a way to really solve this issue, please let me know!

May 31, 2013

Comments

Please login to comment.
Latest blogs
Join the Content Recommendations Work Smarter webinar May 8th 16.00-16.45 CET with expert Aidan Swain

Learn more about Content Recommendations, with Optimizely’s very own Senior Solutions consultant, Aidan Swain . He will discuss best practices and...

Karen McDougall | Apr 12, 2024

Plugin for Identifying and Bulk Deleting Orphaned Missing Properties in Optimizely

I am aware that the Optimizely World community has extensively discussed this topic, with numerous solutions and code snippets available to help...

Adnan Zameer | Apr 11, 2024 | Syndicated blog

Enhancing the Authoring Experience: Extending the LinkItem

The LinkItem field is one of the most demanded properties by the community, allowing editors to effortlessly create and manage links across pages a...

Santiago Morla | Apr 10, 2024 | Syndicated blog

The distinctions between Optimizely Web Experimentation and Optimizely Feature Experimentation

This blog is part of the series - Unlocking the Power of Experimentation: A Marketer's Insight. Let’s dive into Optimizely's powerful experimentati...

Holly Quilter | Apr 9, 2024

Optimizely SaaS CMS: Balancing TCO and ROI in Your CMS Hosting Decision

With Optimizely SaaS CMS coming soon, I’ve been talking with companies about the tricky business of picking the right core system software for thei...

Johnny Mullaney | Apr 8, 2024 | Syndicated blog

What version of Optimizely CMS am I running?

Optimizely continually rolls out new features for CMS customers and these features are normally for the latest major version of Optimizely CMS (CMS...

David Knipe | Apr 8, 2024 | Syndicated blog