| /*** EventsUpgrade ***/ if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}EventsUpgrade]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure {databaseOwner}[{objectQualifier}EventsUpgrade] GO CREATE PROCEDURE {databaseOwner}{objectQualifier}EventsUpgrade AS -- Copy over Moderators from ModuleSettings INSERT INTO {databaseOwner}{objectQualifier}ModulePermission ( RoleID, ModuleID, PermissionID, AllowAccess ) SELECT {databaseOwner}{objectQualifier}ModuleSettings.SettingValue AS RoleID, {databaseOwner}{objectQualifier}ModuleSettings.ModuleID, {databaseOwner}{objectQualifier}Permission.PermissionID, - 1 AS AllowAccess FROM {databaseOwner}{objectQualifier}ModuleSettings INNER JOIN {databaseOwner}{objectQualifier}Modules ON {databaseOwner}{objectQualifier}ModuleSettings.ModuleID = {databaseOwner}{objectQualifier}Modules.ModuleID INNER JOIN {databaseOwner}{objectQualifier}ModuleDefinitions ON {databaseOwner}{objectQualifier}Modules.ModuleDefID = {databaseOwner}{objectQualifier}ModuleDefinitions.ModuleDefID CROSS JOIN {databaseOwner}{objectQualifier}Permission WHERE ({databaseOwner}{objectQualifier}ModuleSettings.SettingName = 'moderatorroleid') AND ({databaseOwner}{objectQualifier}ModuleSettings.SettingValue <> '0') AND ({databaseOwner}{objectQualifier}ModuleDefinitions.FriendlyName = N'Events') AND ({databaseOwner}{objectQualifier}Permission.PermissionCode = 'EVENTS_MODULE') AND ({databaseOwner}{objectQualifier}Permission.PermissionKey = 'EVENTSMOD') -- Ensure Moderators have edit permissions INSERT INTO {databaseOwner}{objectQualifier}ModulePermission ( RoleID, ModuleID, PermissionID, AllowAccess ) SELECT MP1.RoleID, MP1.ModuleID, P3.PermissionID, - 1 AS AllowAcces FROM {databaseOwner}{objectQualifier}ModulePermission AS MP1 INNER JOIN {databaseOwner}{objectQualifier}Permission AS P1 ON MP1.PermissionID = P1.PermissionID CROSS JOIN {databaseOwner}{objectQualifier}Permission AS P3 WHERE (P1.PermissionCode = 'Events_Module') AND (P1.PermissionKey = 'EVENTSMOD') AND (MP1.RoleID NOT IN (SELECT MP2.RoleID FROM {databaseOwner}{objectQualifier}ModulePermission AS MP2 INNER JOIN {databaseOwner}{objectQualifier}Permission AS P2 ON MP2.PermissionID = P2.PermissionID INNER JOIN {databaseOwner}{objectQualifier}Modules AS M2 ON MP2.ModuleID = M2.ModuleID INNER JOIN {databaseOwner}{objectQualifier}ModuleDefinitions AS MD2 ON M2.ModuleDefID = MD2.ModuleDefID WHERE (P2.PermissionCode = 'SYSTEM_MODULE_DEFINITION') AND (P2.PermissionKey = 'EDIT') AND (MD2.FriendlyName = N'Events') AND (M2.ModuleID = MP1.ModuleID))) AND (P3.PermissionCode = 'SYSTEM_MODULE_DEFINITION') AND (P3.PermissionKey = 'EDIT') -- Delete Moderator settings from ModuleSettings DELETE FROM {databaseOwner}{objectQualifier}ModuleSettings FROM {databaseOwner}{objectQualifier}ModuleSettings INNER JOIN {databaseOwner}{objectQualifier}Modules ON {databaseOwner}{objectQualifier}ModuleSettings.ModuleID = {databaseOwner}{objectQualifier}Modules.ModuleID INNER JOIN {databaseOwner}{objectQualifier}ModuleDefinitions ON {databaseOwner}{objectQualifier}Modules.ModuleDefID = {databaseOwner}{objectQualifier}ModuleDefinitions.ModuleDefID WHERE ({databaseOwner}{objectQualifier}ModuleSettings.SettingName = 'moderatorroleid') AND ({databaseOwner}{objectQualifier}ModuleDefinitions.FriendlyName = N'Events') GO |