Views: 2427
Number of votes: 1
Average rating:

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

Please login to comment.