-- exec cs_Section_Permissions_Get 1000, 0
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[csm_MailingList_Permissions_Get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[csm_MailingList_Permissions_Get]
GO

create procedure [dbo].csm_MailingList_Permissions_Get
(
	@SettingsID int
)
AS

SET Transaction Isolation Level Read UNCOMMITTED

	-- Return product permissions
	SELECT
		P.SettingsID, 
		Implied = convert(bit, 1), 
		SectionID = -1,
		EmailAddress = null,
		ApplicationKey = null,
		R.RoleName, 
		P.RoleID, 
		P.ApplicationType,
		AllowMask = convert(bigint, coalesce(P.AllowMask, 0x00)), 
		DenyMask = convert(bigint, coalesce(P.DenyMask, 0x00))
	FROM
		cs_ProductPermissions P,
		aspnet_Roles R
	WHERE 
		P.RoleID = R.RoleId AND
		P.SettingsID = @SettingsID

	UNION

	-- Return section permissions
	SELECT
		P.SettingsID, 
		Implied = convert(bit, 0), 
		P.SectionID, 
		M.EmailAddress,
		F.ApplicationKey,
		R.RoleName, 
		P.RoleID, 
		F.ApplicationType,
		AllowMask = convert(bigint, coalesce(P.AllowMask, 0x00)),  
		DenyMask = convert(bigint, coalesce(P.DenyMask, 0x00))  
	FROM 
		cs_sectionpermissions P,
		aspnet_Roles r,
		csm_MailingLists M,
		cs_Sections F
	WHERE
		P.RoleID = r.RoleId AND
		P.SettingsID = @SettingsID AND
		P.SectionID = M.SectionID AND
		F.SectionID = M.SectionID AND
		M.IsMailingList = 1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[csm_MailingList_Permissions_Get] to public
go