I believe I've traced the problem to the stored procedures [mp_Users_SelectLockedPage] and [mp_Users_SelectNotApprovedPage]. I created a virgin mojoPortal database from file mojoportal-2-3-6-7-mssql-net40-deploymentfiles.zip to double check I hadn't corrupted them myself.
Both of these stored procedures use the literal numeric value 1 instead of the sql variable @SiteID, and that's why my approved users were not showing up in the list.
I edited the stored proc lines "WHERE SiteID = 1" to "WHERE SiteID = @SiteID" to fix.
Here they are:
USE [mojo2-3-6-7]
GO
/****** Object: StoredProcedure [dbo].[mp_Users_SelectNotApprovedPage] Script Date: 12/16/2011 09:42:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[mp_Users_SelectNotApprovedPage]
/*
Author: Joe Audette
Created: 2011-01-17
Last Modified: 2011-01-17
*/
@SiteID int,
@PageNumber int,
@PageSize int
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = (@PageSize * @PageNumber) - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
CREATE TABLE #PageIndexForUsers
(
IndexID int IDENTITY (1, 1) NOT NULL,
UserID int
)
BEGIN
INSERT INTO #PageIndexForUsers (UserID)
SELECT UserID
FROM mp_Users
WHERE
SiteID = 1
AND ApprovedForForums = 0
ORDER BY [Name]
END
SELECT u.*
FROM mp_Users u
JOIN #PageIndexForUsers p
ON u.UserID = p.UserID
WHERE
u.SiteID = 1
AND p.IndexID > @PageLowerBound
AND p.IndexID < @PageUpperBound
ORDER BY p.IndexID
DROP TABLE #PageIndexForUsers
GO
USE [mojo2-3-6-7]
GO
/****** Object: StoredProcedure [dbo].[mp_Users_SelectLockedPage] Script Date: 12/16/2011 09:42:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[mp_Users_SelectLockedPage]
/*
Author: Joe Audette
Created: 2010-06-02
Last Modified: 2010-06-02
*/
@SiteID int,
@PageNumber int,
@PageSize int
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = (@PageSize * @PageNumber) - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
CREATE TABLE #PageIndexForUsers
(
IndexID int IDENTITY (1, 1) NOT NULL,
UserID int
)
BEGIN
INSERT INTO #PageIndexForUsers (UserID)
SELECT UserID
FROM mp_Users
WHERE
SiteID = 1
AND IsLockedOut = 1
ORDER BY [Name]
END
SELECT u.*
FROM mp_Users u
JOIN #PageIndexForUsers p
ON u.UserID = p.UserID
WHERE
u.SiteID = 1
AND p.IndexID > @PageLowerBound
AND p.IndexID < @PageUpperBound
ORDER BY p.IndexID
DROP TABLE #PageIndexForUsers
GO