Unpublished Posts are only shown to owner

[SCullman]

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!

[valadas]

Using the latest release, if I login as an administrator, I see the unpublished posts from other users blogs. Is this issue something else I do not understand? @SCullman

[aaronsglz]

Hi @valadas, I understand that @SCullman means that if a user has permission to post on a blog, they can't see other users' drafts. I would imagine this is the case because I had a similar problem.