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!
Comments