If you're running DNN 5.5, you'll need the following script untill I can prepare a 1.3.8 build. If you don't already have the 1.3.7 build, send me an email or check Showcovered to see if I've uploaded it yet.
[Edited 10/31/2010 by dw to fix another issue with the script when running a new install of metaPost. Script can now be run for both clean install and upgrade.]
-- ************Script Follows***************
-- Only create sproc if version of DNN is greater than 5.05.00
-- Otherwise, we'll get an error.
IF EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}Version WHERE Cast(Major as decimal) + Cast(Minor as decimal)/100.00 + Cast(Build as decimal)/10000.00 >= 5.05)
AND EXISTS (SELECT 1 FROM dbo.sysobjects WHERE name LIKE '%itcMetaPost_Get_Modules_With_Edit_Permission')
DROP PROCEDURE {databaseOwner}{objectQualifier}itcMetaPost_Get_Modules_With_Edit_Permission
IF EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}Version WHERE Cast(Major as decimal) + Cast(Minor as decimal)/100.00 + Cast(Build as decimal)/10000.00 >= 5.05)
BEGIN
Exec ('
-- =============================================
-- Procedure: itcMetaPost_Get_Modules_With_Edit_Permission
-- Author: Don Worthley
-- Create date: 02/13/2008
-- Description: Retrieve the list of modules which a user has access to edit
-- based on FriendlyName, UserId and PortalId.
--
-- History:
-- DW - 06/19/2008 - Changed sort to alpha sort by module title
-- DW - 08/12/2009 - Wrapped the ModuleTitle column with an IsNull
-- DW - 08/28/2010 - Updated the sproc to use the new vw_Modules view since the
-- ModuleTitle column was removed from the Modules table.
--
-- EXEC itcMetaPost_Get_Modules_With_Edit_Permission ''HTML Pro'', 1, 0
--
-- =============================================
CREATE PROCEDURE {databaseOwner}{objectQualifier}itcMetaPost_Get_Modules_With_Edit_Permission
@FriendlyName nvarchar(128) = NULL,
@UserId int = 0,
@PortalId int = 0
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
-- Check to see if user is SuperUser or Admin
DECLARE @IsSuperUser bit
DECLARE @IsAdmin bit
DECLARE @PortalAdminRoleId int
DECLARE @IsPrivileged bit
SELECT @IsSuperUser = IsSuperUser FROM {databaseOwner}{objectQualifier}Users WHERE UserId = @UserId
SELECT @PortalAdminRoleId = AdministratorRoleId FROM {databaseOwner}{objectQualifier}Portals WHERE PortalId = @PortalId
SELECT @IsAdmin = CASE WHEN EXISTS (
SELECT * FROM {databaseOwner}{objectQualifier}Users u
JOIN {databaseOwner}{objectQualifier}UserRoles ur ON u.UserId = ur.UserId
JOIN {databaseOwner}{objectQualifier}Roles r ON (r.RoleId = ur.RoleId)
WHERE u.UserId = @UserId
AND r.PortalId = @PortalId
AND r.RoleId = @PortalAdminRoleId
) THEN 1 ELSE 0 END
SELECT @IsPrivileged = CASE WHEN @IsAdmin = 1 OR @IsSuperUser = 1 THEN 1 ELSE 0 END
SELECT DISTINCT
m.ModuleId
,t.TabId
,tm.TabModuleId
,IsNull(m.ModuleTitle, '''') as ModuleTitle
,t.TabPath
,[CreatedDate] = GetDate()
FROM {databaseOwner}{objectQualifier}vw_Modules m
JOIN {databaseOwner}{objectQualifier}ModuleDefinitions md ON m.ModuleDefID = md.ModuleDefID
JOIN {databaseOwner}{objectQualifier}DesktopModules dm ON md.DesktopModuleID = dm.DesktopModuleID
JOIN {databaseOwner}{objectQualifier}TabModules tm ON tm.ModuleID = m.ModuleID
JOIN {databaseOwner}{objectQualifier}TABS t ON t.TabID = tm.TabId
LEFT JOIN {databaseOwner}{objectQualifier}TabPermission tp on t.TabID = tp.TabID
LEFT JOIN {databaseOwner}{objectQualifier}ModulePermission mp on mp.ModuleID = m.ModuleID
WHERE dm.FriendlyName = @FriendlyName
AND tp.AllowAccess = 1
AND m.PortalId = @PortalId
AND t.DisableLink = 0
AND t.IsDeleted = 0
AND
(@IsPrivileged = 1 OR (
(tp.RoleID IN (
SELECT RoleID FROM {databaseOwner}{objectQualifier}USERS u
JOIN {databaseOwner}{objectQualifier}UserRoles ur ON ur.UserID = u.UserID
WHERE u.UserID = @UserID)
OR
tp.UserID = @UserID)
AND tp.PermissionID = 4
)
OR
(
(mp.RoleID IN (
SELECT RoleID FROM {databaseOwner}{objectQualifier}USERS u
JOIN {databaseOwner}{objectQualifier}UserRoles ur ON ur.UserID = u.UserID
WHERE u.UserID = @UserID)
OR
mp.UserID = @UserID)
AND mp.PermissionID = 2
))
ORDER BY IsNull(m.ModuleTitle, ''''), t.TabPath
END
')
END
-- ************End of Script***************