SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Removing schema binding from [dbo].[v_FarmDescriptions]'
GO
ALTER VIEW [dbo].[v_FarmDescriptions]
AS 
WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	 IsAdminTable (IsAdmin) 
	 AS
	 (
			SELECT TOP 1 v.IsAdmin 
			FROM [dbo].[v_IsUserAdmin] v
	 ),
	 MySPDocKitRoleFarms 
	 AS
	 (
			SELECT DISTINCT rf.FarmID
			FROM [dbo].[SPDocKitRoleFarms] rf
			JOIN [dbo].[v_MySPDocKitRoles] r ON r.ID = rf.RoleID
	 )
  SELECT fd.[ID]
      ,fd.[Name]
      ,fd.[FarmData]
      ,fd.[IsSnapshot]
      ,fd.[IsManual]
      ,fd.[IsBackup]
      ,fd.[DifferencesDetected]
      ,fd.[CreatedBy]
      ,fd.[LoadingInfoID]
      ,fd.[IsGoodConfiguration]
	  ,f.[ID] as FarmID
	  ,f.[FarmGuid]
	  ,l.[CreatedOn]
  FROM SecurityEnabled sec, IsAdminTable a, [dbo].[FarmDescriptions] fd
  INNER JOIN [dbo].[LoadingInfos] l ON l.ID = fd.LoadingInfoID
  INNER JOIN [dbo].[Farms] f ON f.ID = l.FarmID
  WHERE sec.RBSecurityEnabled = 0 OR a.IsAdmin = 1 OR f.ID IN (SELECT myFarms.FarmID FROM MySPDocKitRoleFarms myFarms)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping [dbo].[GetRoleAssignments]'
GO
DROP PROCEDURE [dbo].[GetRoleAssignments]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping [dbo].[GetFlattenedRoleAssignments]'
GO
DROP PROCEDURE [dbo].[GetFlattenedRoleAssignments]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[FarmDescriptions]'
GO
ALTER TABLE [dbo].[FarmDescriptions] ADD
[SnapshotId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_FarmDescription_SnapshotId] DEFAULT (newid())
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetFlattenedGroupMembers]'
GO
ALTER PROCEDURE [dbo].[GetFlattenedGroupMembers]
	@PrincipalIDs FilterIDsType READONLY,
	@FarmID int,
	@LoadingInfoID int,
	@PrincipalTypeIDs FilterIDsType READONLY,
	@PrincipalStatusIDs FilterIDsType READONLY
AS

	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_PrincipalIDs FilterIDsType
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_PrincipalTypeIDs FilterIDsType
	DECLARE	@Loc_PrincipalStatusIDs FilterIDsType

	SELECT @Loc_FarmID  = @FarmID,
		  @Loc_LoadingInfoID = @LoadingInfoID 

	INSERT INTO @Loc_PrincipalIDs
	SELECT * FROM @PrincipalIDs
	
	INSERT INTO @Loc_PrincipalTypeIDs
	SELECT * FROM @PrincipalTypeIDs
	
	INSERT INTO @Loc_PrincipalStatusIDs
	SELECT * FROM @PrincipalStatusIDs	

	IF OBJECT_ID('tempdb..#OrganisationDescendantFlattenGroupMembers') IS NOT NULL
		DROP TABLE #OrganisationDescendantFlattenGroupMembers

	CREATE TABLE #OrganisationDescendantFlattenGroupMembers(                   
                    [PrincipalID] INT, 
                    [ContextID] INT,                    
                    [PathLevel] INT,
					[ParentID] INT                   
                    )

	DECLARE @Depth          INT
    DECLARE @NumberOfNewRows   INT                    
	DECLARE @MaxDepth   INT   	                  

    SET @Depth = 0
    SET @NumberOfNewRows = 1
	SET @MaxDepth = 32

    WHILE @Depth <= @MaxDepth and @NumberOfNewRows > 0 
	    BEGIN
		    IF @Depth = 0
			    BEGIN
				    INSERT INTO #OrganisationDescendantFlattenGroupMembers([PrincipalID], [ContextID], [PathLevel], [ParentID])
				    SELECT principals.PrincipalID, principals.PrincipalID, 0 AS PathLevel, NULL
				    FROM Principals AS principals                     
					INNER JOIN @Loc_PrincipalIDs filter ON principals.PrincipalID = filter.ID
			    END
		    ELSE
			    BEGIN
				    INSERT INTO #OrganisationDescendantFlattenGroupMembers([PrincipalID], [ContextID],[PathLevel], [ParentID])
					    SELECT Principals.PrincipalID, org.ContextID AS ContextId, @Depth, org.PrincipalID
					    FROM PrincipalMemberships 
					    JOIN #OrganisationDescendantFlattenGroupMembers AS org on PrincipalMemberships.GroupID = org.PrincipalID and PrincipalMemberships.LoadingInfoID = @Loc_LoadingInfoID and org.PathLevel = (@Depth-1)
					    JOIN Principals ON PrincipalMemberships.UserID = Principals.PrincipalID 			
                        WHERE org.ContextID <> Principals.PrincipalID
		    END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END

	SELECT	                                                    
		o.PrincipalID,
		currentPrincipal.PrincipalTypeID,
		currentPrincipal.DisplayName,
		currentPrincipalLoaded.AccountEnabled,
		o.ParentID,
		o.ContextID as RootParentID,
		currentPrincipal.Email,
		currentPrincipal.Name,
		currentPrincipal.ExternalUser,
		currentPrincipalType.Name as PrincipalType
    FROM #OrganisationDescendantFlattenGroupMembers o 
	JOIN Principals currentPrincipal ON o.PrincipalID = currentPrincipal.PrincipalID
	JOIN PrincipalTypes currentPrincipalType ON currentPrincipalType.PrincipalTypeID = currentPrincipal.PrincipalTypeID 
	JOIN LoadedPrincipals currentPrincipalLoaded ON currentPrincipalLoaded.PrincipalID = currentPrincipal.PrincipalID AND currentPrincipalLoaded.LoadingInfoID = @Loc_LoadingInfoID
	WHERE (NOT EXISTS (SELECT 1 FROM @Loc_PrincipalTypeIDs) OR EXISTS ( SELECT 1 FROM @Loc_PrincipalTypeIDs pt WHERE pt.ID = currentPrincipal.PrincipalTypeID))
	AND (NOT EXISTS (SELECT 1 FROM @Loc_PrincipalStatusIDs) OR EXISTS ( SELECT 1 FROM @Loc_PrincipalStatusIDs ps WHERE ps.ID = currentPrincipalLoaded.AccountEnabled))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetFlattenedRoleAssignmentsForScopeIds]'
GO
ALTER PROCEDURE [dbo].[GetFlattenedRoleAssignmentsForScopeIds]
	@Scopes FilterIDsType READONLY,
	@FarmID int,
	@LoadingInfoID int,
	@PrincipalTypeIDs FilterIDsType READONLY,
	@PrincipalStatusIDs FilterIDsType READONLY,
	@RoleIDs FilterIDsType READONLY,
	@MaxDepth int = 32
AS
	SET NOCOUNT ON

	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_Scopes FilterIDsType
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_PrincipalTypeIDs FilterIDsType
	DECLARE	@Loc_PrincipalStatusIDs FilterIDsType
	DECLARE	@Loc_RoleIDs FilterIDsType

	SELECT @Loc_FarmID  = @FarmID,
		  @Loc_LoadingInfoID = @LoadingInfoID 

	INSERT INTO @Loc_Scopes
	SELECT * FROM @Scopes
	
	INSERT INTO @Loc_PrincipalTypeIDs
	SELECT * FROM @PrincipalTypeIDs
	
	INSERT INTO @Loc_PrincipalStatusIDs
	SELECT * FROM @PrincipalStatusIDs
	
	INSERT INTO @Loc_RoleIDs
	SELECT * FROM @RoleIDs
	
	IF OBJECT_ID('tempdb..#OrganisationDescendantFlattenRoleAssignments') IS NOT NULL
		DROP TABLE #OrganisationDescendantFlattenRoleAssignments

	CREATE TABLE #OrganisationDescendantFlattenRoleAssignments(                   
                    [PrincipalID] INT, 
                    [ContextID] INT,                    
                    [PathLevel] INT                   
                    )

    DECLARE @Principals TABLE (                                       
                        [PrincipalID] INT                                      
                    )   

    INSERT INTO @Principals([PrincipalID])
		    SELECT DISTINCT c.PrincipalID
		    FROM dbo.Principals AS c 
            JOIN RoleAssignments ON RoleAssignments.PrincipalID=c.PrincipalID AND RoleAssignments.LoadingInfoID = @Loc_LoadingInfoID
            JOIN @Loc_Scopes AS s ON RoleAssignments.ScopeID = s.ID	

    DECLARE @Depth          INT
    DECLARE @NumberOfNewRows   INT                     

    SET @Depth = 0
    SET @NumberOfNewRows = 1
    WHILE @Depth <= @MaxDepth and @NumberOfNewRows > 0 
	    BEGIN
		    IF @Depth = 0
			    BEGIN
				    INSERT INTO #OrganisationDescendantFlattenRoleAssignments([PrincipalID],[ContextID], [PathLevel])
				    SELECT principals.PrincipalID, principals.PrincipalID, 0 AS PathLevel
				    FROM @Principals AS principals 
			    END
		    ELSE
			    BEGIN
				    INSERT INTO #OrganisationDescendantFlattenRoleAssignments([PrincipalID],[ContextID], [PathLevel])
					    SELECT Principals.PrincipalID, org.ContextID AS ContextId, @Depth
					    FROM PrincipalMemberships 
					    JOIN #OrganisationDescendantFlattenRoleAssignments AS org on PrincipalMemberships.GroupID = org.PrincipalID and PrincipalMemberships.LoadingInfoID = @Loc_LoadingInfoID and org.PathLevel = (@Depth-1)
					    JOIN Principals ON PrincipalMemberships.UserID = Principals.PrincipalID                        
                        WHERE org.ContextID <> Principals.PrincipalID
		    END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END
		
    SELECT	
    s.ID as ScopeID,
    Roles.Title AS Title,
    currentPrincipal.DisplayName As PrincipalName,
	currentPrincipal.Name as PrincipalUsername,
    currentPrincipalType.Name As Type,
    Roles.RoleID AS [ID],
    o.PrincipalID AS PrincipalID,                                                
    currentPrincipalType.PrincipalTypeID as PrincipalTypeID,
    currentPrincipalLoaded.AccountEnabled,  
    CASE
		WHEN (o.PathLevel=0) 
		THEN 1
		ELSE 0
	END AS [GivenDirectly],
	o.ContextID AS ParentPrincipalID,
    CASE
		WHEN (o.PrincipalID=o.ContextId) 
		THEN 'Given Directly'
		ELSE Principals.DisplayName
	END AS ParentDisplayName,
	currentPrincipal.Email as PrincipalEmail,
	currentPrincipal.ExternalUser
    FROM @Loc_Scopes as s
    JOIN RoleAssignments ON RoleAssignments.ScopeID = s.ID AND RoleAssignments.LoadingInfoID = @Loc_LoadingInfoID
    Join #OrganisationDescendantFlattenRoleAssignments as o on o.ContextID=RoleAssignments.PrincipalID
    JOIN Principals ON o.ContextID = Principals.PrincipalID
    JOIN Roles ON RoleAssignments.RoleID=Roles.RoleID
    JOIN Principals currentPrincipal ON o.PrincipalID = currentPrincipal.PrincipalID
	JOIN PrincipalTypes currentPrincipalType ON currentPrincipalType.PrincipalTypeID = currentPrincipal.PrincipalTypeID 
	JOIN LoadedPrincipals currentPrincipalLoaded ON currentPrincipalLoaded.PrincipalID = currentPrincipal.PrincipalID AND currentPrincipalLoaded.LoadingInfoID = @Loc_LoadingInfoID
                                                                   
    WHERE (NOT EXISTS (SELECT 1 FROM @Loc_PrincipalTypeIDs) OR EXISTS ( SELECT 1 FROM @Loc_PrincipalTypeIDs pt WHERE pt.ID = currentPrincipal.PrincipalTypeID))
			AND (NOT EXISTS (SELECT 1 FROM @Loc_PrincipalStatusIDs) OR EXISTS ( SELECT 1 FROM @Loc_PrincipalStatusIDs ps WHERE ps.ID = currentPrincipalLoaded.AccountEnabled))
			AND (NOT EXISTS (SELECT 1 FROM @Loc_RoleIDs) OR EXISTS ( SELECT 1 FROM @Loc_RoleIDs ps WHERE ps.ID = Roles.RoleID))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetRoleAssignmentsForScopeIDs]'
GO
ALTER PROCEDURE [dbo].[GetRoleAssignmentsForScopeIDs]
	@ScopeIDs FilterIDsType READONLY,
	@FarmID int,
	@LoadingInfoID int,
	@PrincipalTypeIDs FilterIDsType READONLY,
	@PrincipalStatusIDs FilterIDsType READONLY,
	@RoleIDs FilterIDsType READONLY
AS
	SET NOCOUNT ON

	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_ScopeIDs FilterIDsType
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_PrincipalTypeIDs FilterIDsType
	DECLARE	@Loc_PrincipalStatusIDs FilterIDsType
	DECLARE	@Loc_RoleIDs FilterIDsType

	SELECT @Loc_FarmID  = @FarmID,
		  @Loc_LoadingInfoID = @LoadingInfoID 

	INSERT INTO @Loc_ScopeIDs
	SELECT * FROM @ScopeIDs
	
	INSERT INTO @Loc_PrincipalTypeIDs
	SELECT * FROM @PrincipalTypeIDs
	
	INSERT INTO @Loc_PrincipalStatusIDs
	SELECT * FROM @PrincipalStatusIDs
	
	INSERT INTO @Loc_RoleIDs
	SELECT * FROM @RoleIDs

	SELECT 
		s.ID as ScopeID,
		Roles.Title AS Title,
		Principals.DisplayName AS PrincipalName,
		Principals.Name as PrincipalUsername,
        PrincipalTypes.Name As Type,
		Roles.RoleID AS [ID],
		Principals.PrincipalID AS PrincipalID,                                                    
		Principals.PrincipalTypeID AS PrincipalTypeID,
		LoadedPrincipals.AccountEnabled,
		1 As [GivenDirectly],
		Principals.PrincipalID AS ParentPrincipalID,                               
		'Given Directly' AS ParentDisplayName,
		Principals.Email as PrincipalEmail,
		Principals.ExternalUser
    FROM @Loc_ScopeIDs s
        JOIN RoleAssignments ON RoleAssignments.ScopeID = s.ID AND RoleAssignments.LoadingInfoID = @Loc_LoadingInfoID
        JOIN Principals ON RoleAssignments.PrincipalID=Principals.PrincipalID
        JOIN PrincipalTypes ON Principals.PrincipalTypeID=PrincipalTypes.PrincipalTypeID
        JOIN Roles ON RoleAssignments.RoleID=Roles.RoleID
        JOIN LoadedPrincipals ON LoadedPrincipals.PrincipalID = Principals.PrincipalID AND LoadedPrincipals.LoadingInfoID = @Loc_LoadingInfoID
    WHERE (NOT EXISTS (SELECT 1 FROM @Loc_PrincipalTypeIDs) OR EXISTS ( SELECT 1 FROM @Loc_PrincipalTypeIDs pt WHERE pt.ID = PrincipalTypes.PrincipalTypeID))
		AND (NOT EXISTS (SELECT 1 FROM @Loc_PrincipalStatusIDs) OR EXISTS ( SELECT 1 FROM @Loc_PrincipalStatusIDs ps WHERE ps.ID = LoadedPrincipals.AccountEnabled))
		AND (NOT EXISTS (SELECT 1 FROM @Loc_RoleIDs) OR EXISTS ( SELECT 1 FROM @Loc_RoleIDs ps WHERE ps.ID = Roles.RoleID))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[v_FarmDescriptions]'
GO
ALTER VIEW [dbo].[v_FarmDescriptions]
WITH SCHEMABINDING
AS 
WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	 IsAdminTable (IsAdmin) 
	 AS
	 (
			SELECT TOP 1 v.IsAdmin 
			FROM [dbo].[v_IsUserAdmin] v
	 ),
	 MySPDocKitRoleFarms 
	 AS
	 (
			SELECT DISTINCT rf.FarmID
			FROM [dbo].[SPDocKitRoleFarms] rf
			JOIN [dbo].[v_MySPDocKitRoles] r ON r.ID = rf.RoleID
	 )
  SELECT fd.[ID]
      ,fd.[Name]
      ,fd.[FarmData]
      ,fd.[IsSnapshot]
      ,fd.[IsManual]
      ,fd.[IsBackup]
      ,fd.[DifferencesDetected]
      ,fd.[CreatedBy]
      ,fd.[LoadingInfoID]
      ,fd.[IsGoodConfiguration]
	  ,fd.[SnapshotId]
	  ,f.[ID] as FarmID
	  ,f.[FarmGuid]
	  ,l.[CreatedOn]
  FROM SecurityEnabled sec, IsAdminTable a, [dbo].[FarmDescriptions] fd
  INNER JOIN [dbo].[LoadingInfos] l ON l.ID = fd.LoadingInfoID
  INNER JOIN [dbo].[Farms] f ON f.ID = l.FarmID
  WHERE sec.RBSecurityEnabled = 0 OR a.IsAdmin = 1 OR f.ID IN (SELECT myFarms.FarmID FROM MySPDocKitRoleFarms myFarms)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetChildrenForParent]'
GO
ALTER PROCEDURE [dbo].[GetChildrenForParent]
	@LoadingInfoID INT,
	@FarmID INT,
	@ParentID INT
AS
BEGIN 
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_ParentID int

	SELECT @Loc_FarmID  = @FarmID,
		@Loc_LoadingInfoID = @LoadingInfoID,
		@Loc_ParentID = @ParentID

	SELECT	[SPObject].[ID],
			[SPObject].[Url], 
			[SPObject].[Name],
			[SPObject].[SPGuid],
			(CASE WHEN EXISTS 
				(
				SELECT [dbo].[SharePointObjectMemberships].[ParentID] FROM [dbo].[SharePointObjectMemberships] WHERE [dbo].[SharePointObjectMemberships].[LoadingInfoID] = @Loc_LoadingInfoID
				AND [dbo].[SharePointObjectMemberships].[ParentID] = [SPObject].[ID]
				) THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END),
			[SPObject].[SPObjectTypeID],
			[SPObject].[HasUniqueRoleAssignments]
	FROM [dbo].[v_SharePointObjects] AS [SPObject]
	JOIN [dbo].[SharePointObjectMemberships] AS [Membership] ON [Membership].[ParentID] = @Loc_ParentID AND [Membership].[LoadingInfoID] = @Loc_LoadingInfoID AND [Membership].[ChildID] = [SPObject].[ID]
	ORDER BY [SPObjectTypeID], [Name]
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetContentTypeListUsage]'
GO
ALTER PROCEDURE [dbo].[GetContentTypeListUsage]
	@SiteCollectionID int,
	@ContentTypeID int,
	@LoadingInfoID int
AS
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_SiteCollectionID int
	DECLARE	@Loc_ContentTypeID int

	SELECT @Loc_SiteCollectionID  = @SiteCollectionID,
	@Loc_LoadingInfoID = @LoadingInfoID,
	@Loc_ContentTypeID = @ContentTypeID

	SELECT spo.ID
	FROM SharePointObjectContentType spct
	JOIN v_SharePointObjects spo ON spo.ID = spct.SharePointObjectID
	WHERE spct.LoadingInfoID = @Loc_LoadingInfoID AND spct.ContentTypeID = @Loc_ContentTypeID AND spo.SiteID = @Loc_SiteCollectionID AND spo.SPObjectTypeID = 3
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetDetailedPermissions]'
GO
ALTER PROCEDURE [dbo].[GetDetailedPermissions]
	@ScopeIDs FilterIDsType READONLY,
	@FarmID int,
	@LoadingInfoID int,
	@PrincipalTypeIDs FilterIDsType READONLY,
	@PermissionTypeIDs FilterIDsType READONLY,
	@MaxDepth int = 32
AS
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_ScopeIDs FilterIDsType
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_PrincipalTypeIDs FilterIDsType
	DECLARE	@Loc_PermissionTypeIDs FilterIDsType

	SELECT @Loc_FarmID  = @FarmID,
		  @Loc_LoadingInfoID = @LoadingInfoID 

	INSERT INTO @Loc_ScopeIDs
	SELECT * FROM @ScopeIDs
	
	INSERT INTO @Loc_PrincipalTypeIDs
	SELECT * FROM @PrincipalTypeIDs
	
	INSERT INTO @Loc_PermissionTypeIDs
	SELECT * FROM @PermissionTypeIDs	
	
    IF OBJECT_ID('tempdb..#OrganisationDescendant') IS NOT NULL
		DROP TABLE #OrganisationDescendant
		
	CREATE TABLE #OrganisationDescendant(
						[PrincipalID] INT, 
						[ContextID] INT,
						[PathLevel] INT
                    )

    DECLARE @Depth          INT
    DECLARE @NumberOfNewRows   INT    

    SET @Depth = 0
    SET @NumberOfNewRows = 1
    WHILE @Depth <= @MaxDepth and @NumberOfNewRows > 0 
	    BEGIN
		    IF @Depth = 0
			    BEGIN
				    INSERT INTO #OrganisationDescendant([PrincipalID],[ContextID], [PathLevel])
				    SELECT principals.PrincipalID, principals.PrincipalID,  0 AS PathLevel
				    FROM dbo.Principals AS principals 
                    JOIN RoleAssignments ON RoleAssignments.PrincipalID=principals.PrincipalID AND RoleAssignments.LoadingInfoID = @Loc_LoadingInfoID	
					JOIN @Loc_ScopeIDs scopes ON scopes.ID = RoleAssignments.ScopeID        
			    END
		    ELSE
			    BEGIN
				    INSERT INTO #OrganisationDescendant([PrincipalID],[ContextID], [PathLevel])
					    SELECT principals.PrincipalID, descendant.ContextID AS ContextId, @Depth
					    FROM PrincipalMemberships 
					    JOIN #OrganisationDescendant as descendant on PrincipalMemberships.GroupID = descendant.PrincipalID and PrincipalMemberships.LoadingInfoID = @Loc_LoadingInfoID and descendant.PathLevel = (@Depth-1)
					    JOIN Principals AS principals on PrincipalMemberships.UserID = principals.PrincipalID                       
                        WHERE descendant.ContextID <> principals.PrincipalID
                END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END
							
							
	SELECT
        scope.ID as [ScopeID],
        org.PrincipalID AS PrincipalID,
        currentPrincipal.DisplayName As PrincipalName,
        currentPrincipal.PrincipalTypeID as PrincipalTypeID,
        currentPrincipalType.Name As PrincipalType,
        currentPrincipalLoaded.AccountEnabled,
        Roles.RoleID AS [RoleID],
        Roles.Title AS RoleTitle,	
        PermissionTypes.Name AS PermissionType,                                            
        org.ContextID AS ParentPrincipalID,
        CASE
            WHEN (org.PrincipalID=org.ContextId) 
            THEN 'Given Directly'
            ELSE GivenThroughPrincipals.DisplayName
        END AS ParentDisplayName,
		currentPrincipal.Email as PrincipalEmail,
		currentPrincipal.Name as PrincipalLogin,
		currentPrincipal.ExternalUser
    FROM @Loc_ScopeIDs scope
    JOIN RoleAssignments ON RoleAssignments.ScopeID = scope.ID AND RoleAssignments.LoadingInfoID = @Loc_LoadingInfoID
    Join #OrganisationDescendant as org on org.ContextID=RoleAssignments.PrincipalID
    JOIN Roles ON RoleAssignments.RoleID=Roles.RoleID
    JOIN RolePermissions ON Roles.RoleID=RolePermissions.RoleID AND RolePermissions.LoadingInfoID = @Loc_LoadingInfoID
    JOIN PermissionTypes ON RolePermissions.PermissionTypeID=PermissionTypes.PermissionTypeID    
	JOIN Principals GivenThroughPrincipals ON org.ContextID=GivenThroughPrincipals.PrincipalID
	JOIN Principals currentPrincipal ON org.PrincipalID = currentPrincipal.PrincipalID
	JOIN PrincipalTypes currentPrincipalType ON currentPrincipalType.PrincipalTypeID = currentPrincipal.PrincipalTypeID 
    JOIN LoadedPrincipals currentPrincipalLoaded ON currentPrincipalLoaded.PrincipalID = currentPrincipal.PrincipalID AND currentPrincipalLoaded.LoadingInfoID = @Loc_LoadingInfoID

    WHERE (NOT EXISTS (SELECT 1 FROM @Loc_PrincipalTypeIDs) OR EXISTS ( SELECT 1 FROM @Loc_PrincipalTypeIDs pt WHERE pt.ID = currentPrincipal.PrincipalTypeID))
		AND (NOT EXISTS (SELECT 1 FROM @Loc_PermissionTypeIDs) OR EXISTS ( SELECT 1 FROM @Loc_PermissionTypeIDs pt WHERE pt.ID = PermissionTypes.PermissionTypeID))
        GROUP BY scope.ID,
			org.ContextId,
			currentPrincipal.DisplayName, 
			GivenThroughPrincipals.DisplayName,
			Roles.RoleID,
			Roles.Title, 
			PermissionTypes.Name,
			currentPrincipal.PrincipalTypeID,
			currentPrincipalType.Name,
			org.PrincipalID, 
			currentPrincipalLoaded.AccountEnabled,
			currentPrincipal.Email,
			currentPrincipal.Name,
			currentPrincipal.ExternalUser
    ORDER BY currentPrincipal.DisplayName
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetFeatureActivations]'
GO
ALTER PROCEDURE [dbo].[GetFeatureActivations]
	@FeatureIDs FilterIDsType READONLY,
	@LoadingInfoID int
AS
	SET NOCOUNT ON	
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_FeatureIDs FilterIDsType

	SELECT @Loc_LoadingInfoID = @LoadingInfoID

	INSERT INTO @Loc_FeatureIDs
	SELECT * FROM @FeatureIDs

	SELECT ScopeID,
        FeatureScopes.Name as 'Scope',
        Title + ' (' + CAST(Features.FeatureGuid as nvarchar(255))+ ')' AS 'Title',
        Features.FeatureGuid as FeatureID,
        CASE WHEN FeaturesActivations.SPObjectID IS NULL
		    THEN 'Not Activated'
		    ELSE 'Activated'
        END as 'ActivatedOn' 
    FROM Features 
    JOIN FeaturesActivations ON Features.ID=FeaturesActivations.FeatureID AND FeaturesActivations.LoadingInfoID=@Loc_LoadingInfoID                              
    JOIN FeatureScopes ON Features.ScopeID=FeatureScopes.ID
    WHERE FeaturesActivations.Active = 1 AND Features.ScopeID IN (0) AND EXISTS (SELECT 1 FROM @Loc_FeatureIDs f WHERE f.ID = Features.ID)

    UNION

    (SELECT ScopeID,
        FeatureScopes.Name as 'Scope',
        Title + ' (' + CAST(Features.FeatureGuid as nvarchar(255))+ ')' AS 'Title',
        Features.FeatureGuid as FeatureID,
        ISNULL (
		    (CASE WHEN spo.SPObjectTypeID=1
		        THEN ISNULL((SELECT TOP 1 s.Name FROM v_SharePointObjects s 
                JOIN SharePointObjectMemberships ON SharePointObjectMemberships.ChildID = s.ID AND SharePointObjectMemberships.ParentID = spo.ID AND SharePointObjectMemberships.LoadingInfoID=@Loc_LoadingInfoID
            ), spo.Name)
		        ELSE  spo.Name 
		        END + ' (' + spo.Url + ')'), 'Not Activated') as 'ActivatedOn' 
    FROM Features 
    JOIN FeaturesActivations ON Features.ID=FeaturesActivations.FeatureID AND FeaturesActivations.LoadingInfoID=@Loc_LoadingInfoID
    JOIN v_SharePointObjects spo ON (FeaturesActivations.SPObjectID IS NOT NULL AND FeaturesActivations.SPObjectID=spo.ID)
    JOIN FeatureScopes ON Features.ScopeID=FeatureScopes.ID
    WHERE FeaturesActivations.Active = 1 AND Features.ScopeID IN (2,3) AND EXISTS (SELECT 1 FROM @Loc_FeatureIDs f WHERE f.ID = Features.ID))
                
    UNION 
                
    (SELECT ScopeID,
        FeatureScopes.Name as 'Scope',
        Title + ' (' + CAST(Features.FeatureGuid as nvarchar(255))+ ')' AS 'Title',
        Features.FeatureGuid as FeatureID,
        ISNULL (w.Name, 'Not Activated') as 'ActivatedOn'                	                    
    FROM Features 
    JOIN FeaturesActivations ON Features.ID=FeaturesActivations.FeatureID AND FeaturesActivations.LoadingInfoID= @Loc_LoadingInfoID                                   
    JOIN v_WebApplications w ON (FeaturesActivations.WebApplicationID IS NOT NULL AND FeaturesActivations.WebApplicationID=w.ID)
    JOIN FeatureScopes ON Features.ScopeID=FeatureScopes.ID
    WHERE FeaturesActivations.Active = 1 AND Features.ScopeID = 1 AND EXISTS (SELECT 1 FROM @Loc_FeatureIDs f WHERE f.ID = Features.ID))
    ORDER BY FeatureScopes.Name ASC, Title ASC
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetFeatureActivationsByEdition]'
GO
ALTER PROCEDURE [dbo].[GetFeatureActivationsByEdition]
	@LoadingInfoID int,
	@WebApplicationIDs FilterIDsType READONLY,
	@SiteCollectionIDs FilterIDsType READONLY
AS
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_WebApplicationIDs FilterIDsType
	DECLARE	@Loc_SiteCollectionIDs FilterIDsType

	SELECT @Loc_LoadingInfoID = @LoadingInfoID

	INSERT INTO @Loc_WebApplicationIDs
	SELECT * FROM @WebApplicationIDs
	
	INSERT INTO @Loc_SiteCollectionIDs
	SELECT * FROM @SiteCollectionIDs
	
	SELECT w.ID,
        w.Name as WebApplicationName,
	    spo.Name AS SiteCollectionName,
        FeatureEditions.ID AS EditionID,
        FeatureEditions.Name AS Edition,
	    Features.FeatureGuid AS FeatureID,
	    Features.Title,
	    Features.Version,	                                
	    Features.Hidden	                                
    FROM Features 
    JOIN FeaturesActivations ON (Features.ID=FeaturesActivations.FeatureID AND FeaturesActivations.WebApplicationID IS NULL AND FeaturesActivations.LoadingInfoID=@Loc_LoadingInfoID) AND FeaturesActivations.Active = 1
    JOIN v_SharePointObjects spo ON FeaturesActivations.SPObjectID = spo.ID AND FeaturesActivations.LoadingInfoID=@Loc_LoadingInfoID AND spo.SPObjectTypeID = 1
    JOIN v_WebApplications w ON spo.WebAppID=w.ID 
    JOIN FeatureEditions ON Features.EditionID = FeatureEditions.ID
	WHERE EXISTS (SELECT * FROM @Loc_WebApplicationIDs as wf WHERE wf.ID=w.ID ) 
		  AND (NOT EXISTS (SELECT 1 FROM @Loc_SiteCollectionIDs) OR EXISTS ( SELECT 1 FROM @Loc_SiteCollectionIDs sc WHERE sc.ID = spo.ID))
    ORDER BY spo.Name, FeatureEditions.ID ASC, Features.Title
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetPathFromSiteCollection]'
GO
ALTER PROCEDURE [dbo].[GetPathFromSiteCollection]
    @ObjectID int,
	@LoadingInfoID int,
	@MaxDepth int
AS
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_ObjectID int
	DECLARE	@Loc_MaxDepth int

	SELECT @Loc_LoadingInfoID = @LoadingInfoID,
		@Loc_ObjectID = @ObjectID,
		@Loc_MaxDepth = @MaxDepth

	DECLARE @OrganisationDescendant TABLE ( 
                    [Name][nvarchar](max),
                    [ID] INT, 
                    [SharePointObjectTypeID] INT,
                    [ParentID] INT NULL,
                    [PathLevel] INT,
                    [HasUniqueRoleAssignments] bit,
                    [IsRootWeb] bit,  
					[PermissionScopeID] INT,
                    [SiteID] INT,
                    [LastUpdated] DateTime,
                    [WebApplicationID] INT,
                    [Url][nvarchar](max)  ,
                    [SPGuid]  [uniqueidentifier],
					[CreatedOn] DateTime 
                    )

    DECLARE @Depth          INT
    DECLARE @NumberOfNewRows   INT

    SET @Depth = 0
    SET @NumberOfNewRows = 1
    WHILE @Depth <= @Loc_MaxDepth and @NumberOfNewRows > 0 
	    BEGIN
		    IF @Depth = 0
			    BEGIN
				    INSERT INTO @OrganisationDescendant([Name], [ID], [SharePointObjectTypeID], [ParentID], [PathLevel], [HasUniqueRoleAssignments], [IsRootWeb], [PermissionScopeID], [SiteID], [LastUpdated], [WebApplicationID], [Url], [SPGuid], [CreatedOn])
				    SELECT o.Name, o.ID, o.SPObjectTypeID, NULL, 0 AS PathLevel, o.[HasUniqueRoleAssignments], o.[IsRootWeb], scope.ScopeID, o.[SiteID], o.[LastUpdated], o.[WebAppID], o.Url, o.[SPGuid], o.[CreatedOn]
				    FROM dbo.v_SharePointObjects AS o  
					JOIN dbo.ScopeAssignments as scope ON scope.SPObjectID = o.ID AND scope.LoadingInfoID = @Loc_LoadingInfoID
					WHERE  o.ID = @Loc_ObjectID                                    				
			    END
		    ELSE
			    BEGIN
				    INSERT INTO @OrganisationDescendant([Name], [ID], [SharePointObjectTypeID], [ParentID], [PathLevel], [HasUniqueRoleAssignments], [IsRootWeb], [PermissionScopeID], [SiteID], [LastUpdated], [WebApplicationID], [Url], [SPGuid], [CreatedOn])
				        SELECT o.Name, o.ID, o.SPObjectTypeID, m.ParentID, @Depth, o.[HasUniqueRoleAssignments], o.[IsRootWeb], scope.ScopeID, o.[SiteID], o.[LastUpdated], o.[WebAppID], o.Url, o.[SPGuid], o.[CreatedOn]
					    FROM SharePointObjectMemberships as m 
					    JOIN @OrganisationDescendant as d on m.ChildID = d.ID and m.LoadingInfoID = @Loc_LoadingInfoID and d.PathLevel = (@Depth-1)
					    JOIN dbo.SharePointObjects AS o on m.ParentID = o.ID 	
						JOIN dbo.ScopeAssignments as scope ON scope.SPObjectID = o.ID AND scope.LoadingInfoID = @Loc_LoadingInfoID 	
		            END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END
       
	SELECT	[ID],
		[Name],
        [SharePointObjectTypeID],
        [ParentID],
        [PathLevel],
        [HasUniqueRoleAssignments],
        [IsRootWeb],  
		[PermissionScopeID],
        [SiteID],
        [LastUpdated],
        [WebApplicationID],
        [Url],
        [SPGuid],
		[CreatedOn]
	FROM  @OrganisationDescendant as org
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetSharePointStructure]'
GO
ALTER PROCEDURE [dbo].[GetSharePointStructure]
	@RootObjectIDs FilterIDsType READONLY,
	@FarmID int,
	@LoadingInfoID int,
	@SPObjectTypeIDs FilterIDsType READONLY,
	@MaxDepth int = 32
AS
	SET NOCOUNT ON

	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_RootObjectIDs FilterIDsType
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_SPObjectTypeIDs FilterIDsType

	SELECT @Loc_FarmID  = @FarmID,
		  @Loc_LoadingInfoID = @LoadingInfoID 

	INSERT INTO @Loc_RootObjectIDs
	SELECT * FROM @RootObjectIDs
	
	INSERT INTO @Loc_SPObjectTypeIDs
	SELECT * FROM @SPObjectTypeIDs
	
	IF OBJECT_ID('tempdb..#OrganisationDescendant') IS NOT NULL
		DROP TABLE #OrganisationDescendant

	CREATE TABLE #OrganisationDescendant ( 
                                        [Name][nvarchar](max),
                                        [ID] INT, 
                                        [SharePointObjectTypeID] INT,
                                        [ParentID] INT NULL,
                                        [PathLevel] INT,
                                        [HasUniqueRoleAssignments] bit,
                                        [IsRootWeb] bit,  
										[PermissionScopeID] INT,
                                        [SiteID] INT,
                                        [LastUpdated] DateTime,
                                        [WebApplicationID] INT,
                                        [Url][nvarchar](max)  ,
                                        [SPGuid] [uniqueidentifier],
										[CreatedOn] DateTime  
                                        )

    DECLARE @Depth          INT
    DECLARE @NumberOfNewRows   INT

    SET @Depth = 0
    SET @NumberOfNewRows = 1
    WHILE @Depth <= @MaxDepth and @NumberOfNewRows > 0 
	    BEGIN
		    IF @Depth = 0
			    BEGIN
				    INSERT INTO #OrganisationDescendant([Name], [ID], [SharePointObjectTypeID], [ParentID], [PathLevel], [HasUniqueRoleAssignments], [IsRootWeb], [PermissionScopeID], [SiteID], [LastUpdated], [WebApplicationID], [Url], [SPGuid], [CreatedOn] )
				    SELECT o.Name, o.ID, o.SPObjectTypeID, NULL, 0 AS PathLevel, o.[HasUniqueRoleAssignments], o.[IsRootWeb], scope.ScopeID, o.[SiteID], o.[LastUpdated], o.[WebAppID], o.Url, o.[SPGuid], o.[CreatedOn] 
				    FROM dbo.v_SharePointObjects AS o  
					JOIN dbo.ScopeAssignments as scope ON scope.SPObjectID = o.ID AND scope.LoadingInfoID = @Loc_LoadingInfoID  
					WHERE o.ID in (SELECT r.ID FROM @Loc_RootObjectIDs r) AND o.FarmID = @Loc_FarmID                                       				
			    END
		    ELSE
			    BEGIN
				    INSERT INTO #OrganisationDescendant([Name], [ID], [SharePointObjectTypeID], [ParentID], [PathLevel], [HasUniqueRoleAssignments], [IsRootWeb], [PermissionScopeID], [SiteID], [LastUpdated], [WebApplicationID], [Url], [SPGuid], [CreatedOn] )
				        SELECT o.Name, o.ID, o.SPObjectTypeID, m.ParentID, @Depth, o.[HasUniqueRoleAssignments], o.[IsRootWeb], scope.ScopeID, o.[SiteID], o.[LastUpdated], o.[WebAppID], o.Url, o.[SPGuid], o.[CreatedOn] 
					    FROM SharePointObjectMemberships as m 
					    JOIN #OrganisationDescendant as d on m.ParentID = d.ID and m.LoadingInfoID = @Loc_LoadingInfoID and d.PathLevel = (@Depth-1)
					    JOIN dbo.SharePointObjects AS o on m.ChildID = o.ID 	
						JOIN dbo.ScopeAssignments as scope ON scope.SPObjectID = o.ID AND scope.LoadingInfoID = @Loc_LoadingInfoID 	
                        WHERE NOT EXISTS (SELECT 1 FROM @Loc_SPObjectTypeIDs) OR EXISTS ( SELECT 1 FROM @Loc_SPObjectTypeIDs spt WHERE spt.ID = o.SPObjectTypeID)
		            END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END
       
	    SELECT	
            [ID],
			[Name],
            [SharePointObjectTypeID],
            [ParentID],
            [PathLevel],
            [HasUniqueRoleAssignments],
            [IsRootWeb],  
			[PermissionScopeID],
            [SiteID],
            [LastUpdated],
            [WebApplicationID],
            [Url],
            [SPGuid],
			[CreatedOn] 
		FROM  #OrganisationDescendant as org
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetSharePointStructureForWebApplications]'
GO
ALTER PROCEDURE [dbo].[GetSharePointStructureForWebApplications]
	@WebAppIDs FilterIDsType READONLY,
	@FarmID int,
	@LoadingInfoID int,
	@SPObjectTypeIDs FilterIDsType READONLY,
	@MaxDepth int = 32
AS
	SET NOCOUNT ON

	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_WebAppIDs FilterIDsType
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_SPObjectTypeIDs FilterIDsType

	SELECT @Loc_FarmID  = @FarmID,
		  @Loc_LoadingInfoID = @LoadingInfoID 

	INSERT INTO @Loc_WebAppIDs
	SELECT * FROM @WebAppIDs
	
	INSERT INTO @Loc_SPObjectTypeIDs
	SELECT * FROM @SPObjectTypeIDs

	IF OBJECT_ID('tempdb..#OrganisationDescendantWebApps') IS NOT NULL
		DROP TABLE #OrganisationDescendantWebApps

	CREATE TABLE #OrganisationDescendantWebApps ( 
                    [Name][nvarchar](max),
                    [ID] INT, 
                    [SharePointObjectTypeID] INT,
                    [ParentID] INT NULL,
                    [PathLevel] INT,
                    [HasUniqueRoleAssignments] bit,
                    [IsRootWeb] bit,  
					[PermissionScopeID] INT,
                    [SiteID] INT,
                    [LastUpdated] DateTime ,
                    [WebApplicationID] INT,
                    [Url][nvarchar](max),
                    [SPGuid]  [uniqueidentifier],
					[CreatedOn] DateTime       
                    )

    DECLARE @Depth          INT
    DECLARE @NumberOfNewRows   INT

    SET @Depth = 0
    SET @NumberOfNewRows = 1
    WHILE @Depth <= @MaxDepth and @NumberOfNewRows > 0 
	    BEGIN
		    IF @Depth = 0
			    BEGIN
				    INSERT INTO #OrganisationDescendantWebApps([Name], [ID], [SharePointObjectTypeID], [ParentID], [PathLevel], [HasUniqueRoleAssignments], [IsRootWeb], [PermissionScopeID], [SiteID], [LastUpdated], [WebApplicationID], [Url], [SPGuid], [CreatedOn])
				    SELECT o.Name, o.ID, o.SPObjectTypeID, NULL, 0 AS PathLevel, o.[HasUniqueRoleAssignments], o.[IsRootWeb], scope.ScopeID, o.[SiteID], o.[LastUpdated], o.[WebAppID], o.Url,  o.[SPGuid], o.[CreatedOn]
                    FROM dbo.v_WebApplications AS w    			
                    JOIN dbo.LoadedSiteCollections AS ls ON w.ID = ls.WebApplicationID AND ls.LoadingInfoID = @Loc_LoadingInfoID 
                    JOIN dbo.v_SharePointObjects AS o ON ls.SiteCollectionID = o.ID 	                        
					JOIN dbo.ScopeAssignments as scope ON scope.SPObjectID = o.ID AND scope.LoadingInfoID = @Loc_LoadingInfoID  
					WHERE EXISTS (SELECT 1 FROM @WebAppIDs wf WHERE wf.ID = w.ID) AND o.SPObjectTypeID = 1  AND  o.FarmID = @Loc_FarmID                                  				
			    END
		    ELSE
			    BEGIN
				    INSERT INTO #OrganisationDescendantWebApps([Name], [ID], [SharePointObjectTypeID], [ParentID], [PathLevel], [HasUniqueRoleAssignments], [IsRootWeb], [PermissionScopeID], [SiteID], [LastUpdated], [WebApplicationID], [Url], [SPGuid], [CreatedOn])
				        SELECT o.Name, o.ID, o.SPObjectTypeID, m.ParentID, @Depth, o.[HasUniqueRoleAssignments], o.[IsRootWeb], scope.ScopeID, o.[SiteID], o.[LastUpdated], o.[WebAppID], o.Url, o.[SPGuid], o.[CreatedOn]
					    FROM SharePointObjectMemberships as m 
					    JOIN #OrganisationDescendantWebApps as d on m.ParentID = d.ID and m.LoadingInfoID = @Loc_LoadingInfoID and d.PathLevel = (@Depth-1)
					    JOIN dbo.SharePointObjects AS o on m.ChildID = o.ID 	
						JOIN dbo.ScopeAssignments as scope ON scope.SPObjectID = o.ID AND scope.LoadingInfoID = @Loc_LoadingInfoID 	
                        WHERE NOT EXISTS (SELECT 1 FROM @Loc_SPObjectTypeIDs) OR EXISTS (SELECT 1 FROM @Loc_SPObjectTypeIDs spt WHERE spt.ID = o.SPObjectTypeID)
		            END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END
        
		SELECT	
            [ID],
			[Name],
            [SharePointObjectTypeID],
            [ParentID],
            [PathLevel],
            [HasUniqueRoleAssignments],
            [IsRootWeb],  
			[PermissionScopeID],
            [SiteID],
            [LastUpdated],
            [WebApplicationID],
            [Url],
            [SPGuid],
			[CreatedOn]
		FROM  #OrganisationDescendantWebApps as org
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetSiteCollectionsForWebApplication]'
GO
ALTER PROCEDURE [dbo].[GetSiteCollectionsForWebApplication]
	@LoadingInfoID INT,
	@FarmID INT,
	@WebApplicationID INT
AS
BEGIN
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_WebApplicationID int

	SELECT @Loc_FarmID  = @FarmID,
		  @Loc_LoadingInfoID = @LoadingInfoID,
		  @Loc_WebApplicationID = @WebApplicationID

	SELECT	[SiteCollection].[ID],
			[SiteCollection].[Url], 
			[SiteCollection].[Name],
			[SiteCollection].[SPGuid]
	FROM [dbo].[LoadedSiteCollections]
	JOIN [dbo].[v_SharePointObjects] AS [SiteCollection] ON [dbo].[LoadedSiteCollections].[SiteCollectionID] = [SiteCollection].[ID] 
	WHERE [dbo].[LoadedSiteCollections].[LoadingInfoID] = @Loc_LoadingInfoID AND [dbo].[LoadedSiteCollections].[WebApplicationID] = @Loc_WebApplicationID
	ORDER BY [Name]
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetSPWebApplicationsForLoadingInfo]'
GO
ALTER PROCEDURE [dbo].[GetSPWebApplicationsForLoadingInfo]
	@LoadingInfoID INT,
	@FarmID INT
AS
BEGIN
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_LoadingInfoID int

	SELECT @Loc_FarmID  = @FarmID,
		  @Loc_LoadingInfoID = @LoadingInfoID

	SELECT	[WebApplication].[ID],
			[WebApplication].[Url], 
			[WebApplication].[Name],	
			[WebApplication].[SPGuid],			
			(CASE WHEN EXISTS (SELECT [LoadedSiteCollection].[SiteCollectionID]
								FROM [dbo].[LoadedSiteCollections] AS [LoadedSiteCollection] WHERE [LoadedSiteCollection].[LoadingInfoID] = @Loc_LoadingInfoID AND [LoadedSiteCollection].[WebApplicationID] = [WebApplication].[ID])						
				THEN CONVERT(bit, 1)
				ELSE CONVERT(bit, 0) 
			END)
	FROM [dbo].[v_WebApplications] AS [WebApplication]
	JOIN [dbo].[LoadedWebApplications] AS [LoadedWebApplication] ON [LoadedWebApplication].[WebApplicationID] = [WebApplication].[ID] 
	WHERE [WebApplication].[FarmID] = @Loc_FarmID AND [LoadedWebApplication].[LoadingInfoID] = @Loc_LoadingInfoID
	ORDER BY [Name]

END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetSPWebApplicationsForLoadingInfoWithoutChildren]'
GO
ALTER PROCEDURE [dbo].[GetSPWebApplicationsForLoadingInfoWithoutChildren]
	@LoadingInfoID INT,
	@FarmID INT
AS
BEGIN
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_LoadingInfoID int

	SELECT @Loc_FarmID  = @FarmID,
		  @Loc_LoadingInfoID = @LoadingInfoID

	SELECT	[WebApplication].[ID],
			[WebApplication].[Url], 
			[WebApplication].[Name],	
			[WebApplication].[SPGuid]							
	FROM [dbo].[v_WebApplications] AS [WebApplication]
	JOIN [dbo].[LoadedWebApplications] AS [LoadedWebApplication] ON [LoadedWebApplication].[WebApplicationID] = [WebApplication].[ID] 
	WHERE [WebApplication].[FarmID] = @Loc_FarmID AND [LoadedWebApplication].[LoadingInfoID] = @Loc_LoadingInfoID
	ORDER BY [Name]

END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetSubsitesOfSite]'
GO
ALTER PROCEDURE [dbo].[GetSubsitesOfSite]
	@LoadingInfoID INT,
	@FarmID INT,
	@ParentID INT
AS
BEGIN 
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_ParentID int	
	
	SELECT @Loc_FarmID  = @FarmID,
		@Loc_LoadingInfoID = @LoadingInfoID,
		@Loc_ParentID = @ParentID

	SELECT	[SPObject].[ID],
			[SPObject].[Url], 
			[SPObject].[Name],
			[SPObject].[SPGuid]
	FROM [dbo].[v_SharePointObjects] AS [SPObject] 
	JOIN [dbo].[SharePointObjectMemberships] AS [Membership] ON [Membership].[ParentID] = @Loc_ParentID AND [Membership].[LoadingInfoID] = @Loc_LoadingInfoID AND [Membership].[ChildID] = [SPObject].[ID] AND [SPObject].[SPObjectTypeID] = 2
	ORDER BY [Name]

END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetUnusedContent]'
GO
ALTER PROCEDURE [dbo].[GetUnusedContent]
	@RootObjectIDs FilterIDsType READONLY,
	@FarmID int,
	@LoadingInfoID int,
	@ContentTypeIDs FilterIDsType READONLY,
	@UnmodifiedSince datetime,
	@MaxDepth int = 32
AS
	SET NOCOUNT ON

	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_RootObjectIDs FilterIDsType
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_UnmodifiedSince datetime
	DECLARE	@Loc_ContentTypeIDs FilterIDsType

	SELECT @Loc_FarmID  = @FarmID,
		  @Loc_LoadingInfoID = @LoadingInfoID,
		  @Loc_UnmodifiedSince = @UnmodifiedSince

	INSERT INTO @Loc_RootObjectIDs
	SELECT * FROM @RootObjectIDs
	
	INSERT INTO @Loc_ContentTypeIDs
	SELECT * FROM @ContentTypeIDs

	IF OBJECT_ID('tempdb..#OrganisationDescendantUnusedContent') IS NOT NULL
		DROP TABLE #OrganisationDescendantUnusedContent

	CREATE TABLE #OrganisationDescendantUnusedContent ( 
                                        [Name][nvarchar](max),
                                        [ID] INT, 
                                        [SharePointObjectTypeID] INT,
                                        [ParentID] INT NULL,
                                        [PathLevel] INT,
                                        [HasUniqueRoleAssignments] bit,
                                        [IsRootWeb] bit,  
										[PermissionScopeID] INT,
                                        [SiteID] INT,
                                        [LastUpdated] DateTime,
                                        [WebApplicationID] INT,
                                        [Url][nvarchar](max)  ,
                                        [SPGuid] [uniqueidentifier],
										[CreatedOn] DateTime
                                        )

    DECLARE @Depth          INT
    DECLARE @NumberOfNewRows   INT

    SET @Depth = 0
    SET @NumberOfNewRows = 1
    WHILE @Depth <= @MaxDepth and @NumberOfNewRows > 0 
	    BEGIN
		    IF @Depth = 0
			    BEGIN
				    INSERT INTO #OrganisationDescendantUnusedContent([Name], [ID], [SharePointObjectTypeID], [ParentID], [PathLevel], [HasUniqueRoleAssignments], [IsRootWeb], [PermissionScopeID], [SiteID], [LastUpdated], [WebApplicationID], [Url], [SPGuid], [CreatedOn])
				    SELECT o.Name, o.ID, o.SPObjectTypeID, NULL, 0 AS PathLevel, o.[HasUniqueRoleAssignments], o.[IsRootWeb], scope.ScopeID, o.[SiteID], o.[LastUpdated], o.[WebAppID], o.Url, o.[SPGuid], o.[CreatedOn]
				    FROM dbo.v_SharePointObjects AS o  
					JOIN dbo.ScopeAssignments as scope ON scope.SPObjectID = o.ID AND scope.LoadingInfoID = @Loc_LoadingInfoID  
					WHERE o.ID in (SELECT r.ID FROM @Loc_RootObjectIDs r) AND o.FarmID = @Loc_FarmID                                       				
			    END
		    ELSE
			    BEGIN
				    INSERT INTO #OrganisationDescendantUnusedContent([Name], [ID], [SharePointObjectTypeID], [ParentID], [PathLevel], [HasUniqueRoleAssignments], [IsRootWeb], [PermissionScopeID], [SiteID], [LastUpdated], [WebApplicationID], [Url], [SPGuid], [CreatedOn])
				        SELECT o.Name, o.ID, o.SPObjectTypeID, m.ParentID, @Depth, o.[HasUniqueRoleAssignments], o.[IsRootWeb], scope.ScopeID, o.[SiteID], o.[LastUpdated], o.[WebAppID], o.Url, o.[SPGuid], o.[CreatedOn]
					    FROM SharePointObjectMemberships as m 
					    JOIN #OrganisationDescendantUnusedContent as d on m.ParentID = d.ID and m.LoadingInfoID = @Loc_LoadingInfoID and d.PathLevel = (@Depth-1)
					    JOIN dbo.SharePointObjects AS o on m.ChildID = o.ID 	
						JOIN dbo.ScopeAssignments as scope ON scope.SPObjectID = o.ID AND scope.LoadingInfoID = @Loc_LoadingInfoID 	
						WHERE o.SPObjectTypeID <> 4 OR (o.SPObjectTypeID = 4 AND o.LastUpdated < @Loc_UnmodifiedSince)
		        END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END
        SELECT	
            [ID],
			[Name],
            [SharePointObjectTypeID],
            [ParentID],
            [PathLevel],
            [HasUniqueRoleAssignments],
            [IsRootWeb],  
			[PermissionScopeID],
            [SiteID],
            [LastUpdated],
            [WebApplicationID],
            [Url],
            [SPGuid],
			[CreatedOn] 
		FROM  #OrganisationDescendantUnusedContent as org
		WHERE org.SharePointObjectTypeID <> 4 OR (org.SharePointObjectTypeID = 4 
		AND (NOT EXISTS (SELECT 1 FROM @Loc_ContentTypeIDs) 
		OR EXISTS (SELECT 1 FROM @Loc_ContentTypeIDs ct								
								 WHERE ct.ID = -2 AND NOT EXISTS (SELECT 1 FROM SharePointObjectContentType spct2 
																           WHERE spct2.LoadingInfoID = @Loc_LoadingInfoID AND spct2.SharePointObjectID = org.ID))
		OR EXISTS (SELECT 1 FROM @Loc_ContentTypeIDs ct 
							JOIN SharePointObjectContentType spct ON spct.ContentTypeID = ct.ID AND spct.LoadingInfoID = @Loc_LoadingInfoID AND spct.SharePointObjectID = org.ID)))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetUsersPerSite]'
GO
ALTER PROCEDURE [dbo].[GetUsersPerSite]
	@LoadingInfoID int
AS

	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	
	SELECT 	@Loc_LoadingInfoID = @LoadingInfoID

	IF OBJECT_ID('tempdb..#PrincipalsPerSite') IS NOT NULL
		DROP TABLE #PrincipalsPerSite
		
    CREATE TABLE #PrincipalsPerSite
    (              
		[SiteId] INT, 
		[PrincipalId] INT,   
        [Level] INT,
		PRIMARY KEY CLUSTERED
		(
			[SiteId] ASC,
			[PrincipalId] ASC
		)
	) ON [PRIMARY]
	--CREATE NONCLUSTERED INDEX [IX_PPS_Level] ON [#PrincipalsPerSite] 
	--(	
	--	[PrincipalId],
	--	[Level]
	--)
	--INCLUDE ([SiteId])
	--CREATE NONCLUSTERED INDEX [IX_PPS_Level2] ON [#PrincipalsPerSite] 
	--(	
	--	[Level]
	--)
	--INCLUDE ([SiteId], [PrincipalId])
	--CREATE NONCLUSTERED INDEX [IX_PPS_Principals] ON [#PrincipalsPerSite]
	--(
	--	[PrincipalId] ASC,
	--	[IsEnabled] ASC
	--)

    DECLARE @Depth          INT
    DECLARE @MaxDepth       INT
    DECLARE @NumberOfNewRows   INT       
	
    SET @Depth = 0
    SET @MaxDepth = 32
    SET @NumberOfNewRows = 1

    WHILE @Depth <= @MaxDepth and @NumberOfNewRows > 0 
	    BEGIN
            IF @Depth = 0
			    BEGIN
                    INSERT INTO #PrincipalsPerSite([PrincipalId],[SiteId],[Level])
                    SELECT DISTINCT ra.PrincipalID, ra.SiteID, @Depth
                    FROM RoleAssignments as ra
					JOIN [Principals] AS principal 
						ON ra.LoadingInfoID = @Loc_LoadingInfoID 
						AND ra.PrincipalID = principal.PrincipalID
                END
            ELSE
				BEGIN
		            INSERT INTO #PrincipalsPerSite([PrincipalId],[SiteId],[Level])
		            SELECT DISTINCT membership.UserID, pps.SiteId, @Depth
		            FROM #PrincipalsPerSite as pps												
		            JOIN [PrincipalMemberships] AS membership 
						ON membership.GroupID = pps.PrincipalId 
						AND membership.LoadingInfoID = @Loc_LoadingInfoID 												
						AND pps.Level = (@Depth -1)
						AND membership.UserID NOT IN (SELECT PrincipalId FROM #PrincipalsPerSite WHERE #PrincipalsPerSite.SiteId = pps.SiteId)
	            END

		    SET @NumberOfNewRows = @@ROWCOUNT		
            SET @Depth = @Depth + 1
	    END
			
		SELECT 
			CASE	WHEN (GROUPING(lsc.WebApplicationId) = 1 AND GROUPING(pps.SiteId) = 1) THEN 'Farm'
					WHEN (GROUPING(pps.SiteId) = 1) THEN  (SELECT Name FROM WebApplications AS WebApps WHERE WebApps.ID = lsc.webApplicationID ) 
					ELSE (SELECT Name FROM v_SharePointObjects sc WHERE sc.ID = pps.SiteId) 
			END AS 'DisplayName',
			CASE WHEN (GROUPING(lsc.WebApplicationId) = 1) THEN -1 ELSE lsc.WebApplicationID END AS 'WebApplicationId',
			CASE WHEN (GROUPING(pps.SiteId) = 1) THEN -1 ELSE pps.SiteId END AS 'SiteId',						
			COUNT(DISTINCT pps.PrincipalId) AS 'Count',
			CASE WHEN (GROUPING(lp.AccountEnabled) = 1) THEN 'Both' 
					ELSE 
					CASE WHEN lp.AccountEnabled = 1 THEN 'Enabled' ELSE 'Disabled' END
			END AS 'Users'
		FROM #PrincipalsPerSite AS pps
		JOIN LoadedSiteCollections AS lsc ON lsc.LoadingInfoID = @Loc_LoadingInfoID AND pps.SiteId = lsc.SiteCollectionID
		JOIN v_SharePointObjects spo ON spo.ID = lsc.SiteCollectionID
		JOIN LoadedPrincipals AS lp ON lp.LoadingInfoID = @Loc_LoadingInfoID AND lp.PrincipalID = pps.PrincipalId
		WHERE (SELECT PrincipalTypeID FROM Principals AS p WHERE p.PrincipalID = pps.PrincipalId) = 2
		GROUP BY ROLLUP(lsc.WebApplicationID, pps.SiteId), CUBE(lp.AccountEnabled)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetWebApplicationPermissions]'
GO
ALTER PROCEDURE [dbo].[GetWebApplicationPermissions]
	@WebApplicationID int,
	@LoadingInfoID int,
	@PrincipalTypeIDs FilterIDsType READONLY,
	@PrincipalStatusIDs FilterIDsType READONLY
AS
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_WebApplicationID int
	DECLARE	@Loc_PrincipalTypeIDs FilterIDsType
	DECLARE	@Loc_PrincipalStatusIDs FilterIDsType

	SELECT @Loc_WebApplicationID  = @WebApplicationID,
		@Loc_LoadingInfoID = @LoadingInfoID 

	INSERT INTO @Loc_PrincipalTypeIDs
	SELECT * FROM @PrincipalTypeIDs

	INSERT INTO @Loc_PrincipalStatusIDs
	SELECT * FROM @PrincipalStatusIDs

	SELECT PolicyLevels.PolicyLevelID AS [ID],
        PolicyLevels.Name AS Title,                                                    
        Principals.PrincipalID AS PrincipalID,
        Principals.Name AS PrincipalName,
        Principals.PrincipalTypeID AS PrincipalTypeID,
        PrincipalTypes.Name AS Type,
        WebApplicationZones.ZoneID AS ZoneID,
        WebApplicationZones.ZoneName AS ZoneName,
        LoadedPrincipals.AccountEnabled,
		Principals.Email as PrincipalEmail,
		Principals.ExternalUser,
		Principals.DisplayName
        FROM v_WebApplications w
        JOIN WebApplicationPermissions ON WebApplicationPermissions.WebApplicationID=w.ID AND WebApplicationPermissions.LoadingInfoID = @Loc_LoadingInfoID
        JOIN Principals ON WebApplicationPermissions.PrincipalID=Principals.PrincipalID AND WebApplicationPermissions.LoadingInfoID = @Loc_LoadingInfoID
        JOIN LoadedPrincipals ON Principals.PrincipalID=LoadedPrincipals.PrincipalID AND LoadedPrincipals.LoadingInfoID = @Loc_LoadingInfoID
        JOIN PrincipalTypes ON Principals.PrincipalTypeID=PrincipalTypes.PrincipalTypeID
        JOIN PolicyLevels ON WebApplicationPermissions.PolicyLevelID=PolicyLevels.PolicyLevelID
        JOIN WebApplicationZones ON WebApplicationPermissions.ZoneID=WebApplicationZones.ZoneID AND WebApplicationPermissions.LoadingInfoID = @Loc_LoadingInfoID
        WHERE w.ID = @Loc_WebApplicationID
			  AND (NOT EXISTS (SELECT 1 FROM @Loc_PrincipalTypeIDs) OR EXISTS ( SELECT 1 FROM @Loc_PrincipalTypeIDs pt WHERE pt.ID = PrincipalTypes.PrincipalTypeID))
			  AND (NOT EXISTS (SELECT 1 FROM @Loc_PrincipalStatusIDs) OR EXISTS ( SELECT 1 FROM @Loc_PrincipalStatusIDs ps WHERE ps.ID = LoadedPrincipals.AccountEnabled))
        ORDER BY Principals.Name
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
UPDATE Options SET OptionValue = '6.2.0' WHERE OptionID = 14
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

