Unpublished Posts are only shown to owner

Bắt đầu bằng cách tìm thủ tục được lưu trữ Blog_GetPosts được tham chiếu trong issue và so sánh logic lọc của nó với SQL được cung cấp. Xác minh rằng các bài đăng chưa xuất bản được trả về cho người dùng có đặc quyền blog tương ứng, trong khi hành vi hiện có đối với chủ sở hữu và quản trị viên vẫn được giữ nguyên.

Please change SP Blog_GetPosts to

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}Blog_GetPosts') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}Blog_GetPosts
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}Blog_GetPosts
 @ModuleId INT,
 @BlogID INT,
 @DisplayLocale NVARCHAR(10),
 @UserId INT,
 @UserIsAdmin BIT,
 @Published INT,
 @LimitToLocale NVARCHAR(10),
 @EndDate DATETIME,
 @AuthorUserId INT,
 @OnlyActionable BIT,
 @PageIndex INT = -1,
 @PageSize INT = 0,
 @OrderBy VARCHAR(100) = ''
AS
DECLARE 
 @PageLowerBound INT, 
 @PageUpperBound INT, 
 @RowsToReturn INT
EXEC {databaseOwner}{objectQualifier}CalculatePagingInformation @PageIndex, @PageSize, @RowsToReturn output, @PageLowerBound output, @PageUpperBound output;
DECLARE @Blogs TABLE (
 [BlogID] INT,
 [Locale] VARCHAR(10),
 [HasPrivilege] INT,
 [Owner] INT);
INSERT INTO @Blogs
SELECT
 b.BlogId,
 b.Locale,
(SELECT COUNT(*)
 FROM {databaseOwner}{objectQualifier}Blog_BlogPermissions bp
 LEFT JOIN (SELECT ur.UserID, ur.RoleID  
    FROM {databaseOwner}{objectQualifier}UserRoles ur
    WHERE (ur.EffectiveDate <= GETDATE() OR ur.EffectiveDate IS NULL) AND (ur.ExpiryDate >= GETDATE() OR ur.ExpiryDate IS NULL)
    and ur.UserID=@UserID) r  ON r.RoleID=bp.RoleId
 WHERE (bp.UserID=@UserId OR (bp.UserID=-10 AND r.RoleID<>-4) OR bp.RoleId=-1) AND bp.BlogId=b.BlogID AND bp.PermissionId IN (0,1,2)) AS [HasPrivilege],
 b.OwnerUserId
FROM {databaseOwner}{objectQualifier}Blog_Blogs b
WHERE b.ModuleID=@ModuleId;
SELECT
 Tbl.*
FROM
(SELECT
 p.*,
 ROW_NUMBER() OVER
 (ORDER BY
  CASE @OrderBy WHEN 'ALLOWCOMMENTS DESC' THEN p.[AllowComments] END DESC,
  CASE @OrderBy WHEN 'ALLOWCOMMENTS' THEN p.[AllowComments] END ASC,
  CASE @OrderBy WHEN 'ALLOWCOMMENTS ASC' THEN p.[AllowComments] END ASC,
  CASE @OrderBy WHEN 'BLOGID DESC' THEN p.[BlogID] END DESC,
  CASE @OrderBy WHEN 'BLOGID' THEN p.[BlogID] END ASC,
  CASE @OrderBy WHEN 'BLOGID ASC' THEN p.[BlogID] END ASC,
  CASE @OrderBy WHEN 'CONTENTITEMID DESC' THEN p.[ContentItemId] END DESC,
  CASE @OrderBy WHEN 'CONTENTITEMID' THEN p.[ContentItemId] END ASC,
  CASE @OrderBy WHEN 'CONTENTITEMID ASC' THEN p.[ContentItemId] END ASC,
  CASE @OrderBy WHEN 'COPYRIGHT DESC' THEN p.[Copyright] END DESC,
  CASE @OrderBy WHEN 'COPYRIGHT' THEN p.[Copyright] END ASC,
  CASE @OrderBy WHEN 'COPYRIGHT ASC' THEN p.[Copyright] END ASC,
  CASE @OrderBy WHEN 'CREATEDBYUSERID DESC' THEN p.[CreatedByUserId] END DESC,
  CASE @OrderBy WHEN 'CREATEDBYUSERID' THEN p.[CreatedByUserId] END ASC,
  CASE @OrderBy WHEN 'CREATEDBYUSERID ASC' THEN p.[CreatedByUserId] END ASC,
  CASE @OrderBy WHEN 'CREATEDONDATE DESC' THEN p.[CreatedOnDate] END DESC,
  CASE @OrderBy WHEN 'CREATEDONDATE' THEN p.[CreatedOnDate] END ASC,
  CASE @OrderBy WHEN 'CREATEDONDATE ASC' THEN p.[CreatedOnDate] END ASC,
  CASE @OrderBy WHEN 'DISPLAYCOPYRIGHT DESC' THEN p.[DisplayCopyright] END DESC,
  CASE @OrderBy WHEN 'DISPLAYCOPYRIGHT' THEN p.[DisplayCopyright] END ASC,
  CASE @OrderBy WHEN 'DISPLAYCOPYRIGHT ASC' THEN p.[DisplayCopyright] END ASC,
  CASE @OrderBy WHEN 'IMAGE DESC' THEN p.[Image] END DESC,
  CASE @OrderBy WHEN 'IMAGE' THEN p.[Image] END ASC,
  CASE @OrderBy WHEN 'IMAGE ASC' THEN p.[Image] END ASC,
  CASE @OrderBy WHEN 'LASTMODIFIEDBYUSERID DESC' THEN p.[LastModifiedByUserID] END DESC,
  CASE @OrderBy WHEN 'LASTMODIFIEDBYUSERID' THEN p.[LastModifiedByUserID] END ASC,
  CASE @OrderBy WHEN 'LASTMODIFIEDBYUSERID ASC' THEN p.[LastModifiedByUserID] END ASC,
  CASE @OrderBy WHEN 'LASTMODIFIEDONDATE DESC' THEN p.[LastModifiedOnDate] END DESC,
  CASE @OrderBy WHEN 'LASTMODIFIEDONDATE' THEN p.[LastModifiedOnDate] END ASC,
  CASE @OrderBy WHEN 'LASTMODIFIEDONDATE ASC' THEN p.[LastModifiedOnDate] END ASC,
  CASE @OrderBy WHEN 'PUBLISHED DESC' THEN p.[Published] END DESC,
  CASE @OrderBy WHEN 'PUBLISHED' THEN p.[Published] END ASC,
  CASE @OrderBy WHEN 'PUBLISHED ASC' THEN p.[Published] END ASC,
  CASE @OrderBy WHEN 'PUBLISHEDONDATE DESC' THEN p.[PublishedOnDate] END DESC,
  CASE @OrderBy WHEN 'PUBLISHEDONDATE' THEN p.[PublishedOnDate] END ASC,
  CASE @OrderBy WHEN 'PUBLISHEDONDATE ASC' THEN p.[PublishedOnDate] END ASC,
  CASE @OrderBy WHEN 'TITLE DESC' THEN p.[Title] END DESC,
  CASE @OrderBy WHEN 'TITLE' THEN p.[Title] END ASC,
  CASE @OrderBy WHEN 'TITLE ASC' THEN p.[Title] END ASC,
  CASE @OrderBy WHEN 'VIEWCOUNT DESC' THEN p.[ViewCount] END DESC,
  CASE @OrderBy WHEN 'VIEWCOUNT' THEN p.[ViewCount] END ASC,
  CASE @OrderBy WHEN 'VIEWCOUNT ASC' THEN p.[ViewCount] END ASC
) AS RowNum
FROM
 {databaseOwner}{objectQualifier}Blog_PostList(@DisplayLocale) p
 INNER JOIN @Blogs b ON b.BlogID=p.BlogID
WHERE
 (p.BlogID = @BlogID OR @BlogID=-1)
 AND (p.PublishedOnDate < @EndDate OR @EndDate IS NULL)
 AND (p.CreatedByUserId=@AuthorUserId OR @AuthorUserId=-1)
 AND (CAST(p.Published AS INT)=@Published OR @Published=-1)
 AND (p.Published=1 OR p.CreatedByUserID=@UserId OR b.[Owner]=@UserId OR @UserIsAdmin=1  
 -- BEGIN CHANGE Show also Unpublished Posts if User has privilige to
OR b.HasPrivilege > 0
-- BEGIN CHANGE
 ) AND (@LimitToLocale IS NULL OR ISNULL(p.Locale, b.Locale)=@LimitToLocale)
 AND (@OnlyActionable=0 OR (p.CreatedByUserID=@UserId OR b.HasPrivilege>0 OR b.[Owner]=@UserId OR @UserIsAdmin=1))
 ) AS Tbl
WHERE RowNum > @PageLowerBound AND RowNum < @PageUpperBound
ORDER BY RowNum;
SELECT COUNT (*) AS TotalRecords
FROM
 {databaseOwner}{objectQualifier}Blog_PostList(@DisplayLocale) p
 INNER JOIN @Blogs b ON b.BlogID=p.BlogID
WHERE
 (p.BlogID = @BlogID OR @BlogID=-1)
 AND (p.PublishedOnDate < @EndDate OR @EndDate IS NULL)
 AND (p.CreatedByUserId=@AuthorUserId OR @AuthorUserId=-1)
 AND (CAST(p.Published AS INT)=@Published OR @Published=-1)
 AND (p.Published=1 OR p.CreatedByUserID=@UserId OR b.[Owner]=@UserId OR @UserIsAdmin=1  
 -- BEGIN CHANGE Show also Unpublished Posts if User has privilige to
OR b.HasPrivilege > 0
-- BEGIN CHANGE
)
 AND (@LimitToLocale IS NULL OR ISNULL(p.Locale, b.Locale)=@LimitToLocale)
 AND (@OnlyActionable=0 OR (p.CreatedByUserID=@UserId OR b.HasPrivilege>0 OR b.[Owner]=@UserId OR @UserIsAdmin=1))
GO

See comments inside!