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]
	  ,fd.[SnapshotId]
	  ,f.[ID] as FarmID
	  ,f.[FarmGuid]
	  ,l.[CreatedOn]
	  ,f.[FarmType]
	  ,l.[LoadDuration]
	  ,l.[MarkedForDeletion]
	  ,l.[InProgress]
  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'Removing schema binding from [dbo].[v_Farms]'
GO
ALTER VIEW [dbo].[v_Farms]
AS 
  WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	  MyFarms
	  AS
	  (
	        SELECT DISTINCT a.FarmID
			FROM [dbo].[v_SPObjectsWithAccess] a
	  ),
	 MySPDocKitRoleFarms 
	 AS
	 (
			SELECT DISTINCT rf.FarmID 
			FROM [dbo].[SPDocKitRoleFarms] rf
			JOIN [dbo].[v_MySPDocKitRoles] r ON r.ID = rf.RoleID
	 )
	SELECT [ID]
		  ,[FarmGuid]
		  ,[Name]
		  ,[LSE]
		  ,[LEE]
		  ,[LicenseValidTo]
		  ,[Hash]
		  ,[CentralAdministrationUrl]
		  ,[Version]
		  ,[UsageDatabaseConnectionString]
		  ,[DiagnosticsLogLocation]
		  ,[FarmType]
	  FROM SecurityEnabled sec,  [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[Farms] f
	  WHERE sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1
		  OR f.ID IN (SELECT a.FarmID FROM MyFarms a)
		  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'Removing schema binding from [EventCollection].[v_SPDocKitUserFarms]'
GO
ALTER VIEW [EventCollection].[v_SPDocKitUserFarms]
AS	
WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT CAST(case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END AS BIT)
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
RoleFarms AS (select  u.LoginName, u.PrincipalTypeID,  f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
			FROM [dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			INNER JOIN [dbo].[SPDocKitRoleFarms] rf ON rf.RoleID = r.ID
			INNER JOIN [dbo].[Farms] f ON rf.FarmID = f.ID
			UNION 
			SELECT u.LoginName, u.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
			FROM[dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			CROSS APPLY [dbo].[Farms] AS f
			WHERE r.Name ='Admins'),
AllFarms AS (
 SELECT NULL AS 'LoginName', NULL AS 'PrincipalTypeID',  f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
		FROM [dbo].[Farms] AS f)

SELECT  f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
FROM(
	SELECT f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
					  , s.RBSecurityEnabled AS 'ResFlag' FROM RoleFarms AS f CROSS APPLY SecurityEnabled as s
	UNION 
	SELECT f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
					  , ~s.RBSecurityEnabled AS 'ResFlag' FROM AllFarms AS f CROSS APPLY SecurityEnabled as s
	)AS f 
WHERE ResFlag = 1
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 [SQL].[InsertStatisticInfos]'
GO
DROP PROCEDURE [SQL].[InsertStatisticInfos]
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 types'
GO
DROP TYPE [SQL].[StatisticInfoType]
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'Creating types'
GO
CREATE TYPE [SQL].[StatisticInfoType] AS TABLE
(
[LoadedObjectId] [int] NULL,
[StatisticID] [int] NULL,
[StatisticName] [nvarchar] (max) NULL,
[ColumnNames] [nvarchar] (max) NULL,
[IsAutoCreated] [bit] NULL,
[FilterDefinition] [nvarchar] (max) NULL,
[LastUpdated] [datetime] NULL
)
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 [SQL].[LoadedStatistics]'
GO
ALTER TABLE [SQL].[LoadedStatistics] ADD
[LastUpdated] [datetime] NULL
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 [SQL].[LoadedRoleSecurables]'
GO
ALTER TABLE [SQL].[LoadedRoleSecurables] DROP
COLUMN [PermissionName],
COLUMN [PermissionState],
COLUMN [PermissionScope]
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 [SQL].[LoadedColumns]'
GO
ALTER TABLE [SQL].[LoadedColumns] DROP
COLUMN [ColumnName],
COLUMN [MaxLength],
COLUMN [Precision],
COLUMN [CollationName],
COLUMN [IsNullable],
COLUMN [IsIdentity],
COLUMN [Type],
COLUMN [KeyType]
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 [SQL].[LoadedProgrammParameters]'
GO
ALTER TABLE [SQL].[LoadedProgrammParameters] DROP
COLUMN [ParameterName],
COLUMN [HasDefaultValue],
COLUMN [IsOutput],
COLUMN [TypeName]
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'Creating [SQL].[InsertStatisticInfos]'
GO
CREATE PROCEDURE [SQL].[InsertStatisticInfos]
	@TableVariable [SQL].StatisticInfoType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	INSERT INTO [SQL].[LoadedStatistics](
			[LoadedObjectId],
			[StatisticID],
			[StatisticName],
			[ColumnNames],
			[IsAutoCreated],
			[FilterDefinition],
			[LastUpdated])
	SELECT  [LoadedObjectId],
			[StatisticID],
			[StatisticName],
			[ColumnNames],
			[IsAutoCreated],
			[FilterDefinition],
			[LastUpdated]
	FROM @TableVariable statInfo
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].[Farms]'
GO
ALTER TABLE [dbo].[Farms] ADD
[MarkedForDeletion] [bit] NOT NULL CONSTRAINT [DF_Farms_MarkedForDeletion] DEFAULT ((0))
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].[GetUserPermissionAssignments]'
GO
ALTER PROCEDURE [dbo].[GetUserPermissionAssignments]
	@SiteCollectionIDs FilterIDsType READONLY,
	@PrincipalID int,
	@FarmID int,
	@LoadingInfoID int,
	@RoleIDs FilterIDsType READONLY,
	@MaxDepth int = 32
AS
	SET NOCOUNT ON

	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_SiteCollectionIDs FilterIDsType
	DECLARE	@Loc_PrincipalID int
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_RoleIDs FilterIDsType

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

	INSERT INTO @Loc_SiteCollectionIDs
	SELECT * FROM @SiteCollectionIDs
	
	INSERT INTO @Loc_RoleIDs
	SELECT * FROM @RoleIDs

	DECLARE @OrganisationDescendant TABLE ( 
        [DisplayName][nvarchar](max),
        [PrincipalID] INT, 
        [ContextID] INT,
        [PrincipalTypeID] INT,
        [PathLevel] INT,
        [AccountEnabled] bit
        )

    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([DisplayName],[PrincipalID],[ContextID],[PrincipalTypeID],[PathLevel],[AccountEnabled])
				    SELECT Principals.DisplayName, Principals.PrincipalID, Principals.PrincipalID, Principals.PrincipalTypeID, 0 AS PathLevel, LoadedPrincipals.AccountEnabled
				    FROM Principals 
                    JOIN LoadedPrincipals ON LoadedPrincipals.PrincipalID = Principals.PrincipalID AND LoadedPrincipals.LoadingInfoID = @Loc_LoadingInfoID		
                    WHERE Principals.PrincipalID = @Loc_PrincipalID      
					UNION ALL
					SELECT Principals.DisplayName, Principals.PrincipalID, Principals.PrincipalID, Principals.PrincipalTypeID, 0 AS PathLevel, LoadedPrincipals.AccountEnabled
					FROM Principals 
					JOIN LoadedPrincipals ON LoadedPrincipals.PrincipalID = Principals.PrincipalID AND LoadedPrincipals.LoadingInfoID = @Loc_LoadingInfoID		
					WHERE Principals.FarmID = @Loc_FarmID AND Principals.SiteID IS NULL AND Principals.Name IN ('c:0(.s|true','NT AUTHORITY\authenticated users','Everyone', 'c:0!.s|windows')
					AND EXISTS (SELECT 1 FROM Principals WHERE PrincipalID = @Loc_PrincipalID AND PrincipalTypeID = 2) -- Everyone i NT AUTHORITY\authenticated users grupe isto trazi ako je rijec o User tipu
					UNION ALL
					SELECT Principals.DisplayName, Principals.PrincipalID, Principals.PrincipalID, Principals.PrincipalTypeID, 0 AS PathLevel, LoadedPrincipals.AccountEnabled
					FROM Principals 
					JOIN LoadedPrincipals ON LoadedPrincipals.PrincipalID = Principals.PrincipalID AND LoadedPrincipals.LoadingInfoID = @Loc_LoadingInfoID		
					WHERE Principals.FarmID = @Loc_FarmID AND Principals.SiteID IS NULL AND Principals.Name = 'rolemanager|spo-grid-all-users'
					AND EXISTS (SELECT 1 FROM Principals WHERE PrincipalID = @Loc_PrincipalID AND PrincipalTypeID = 2 AND ExternalUser = 0) -- Everyone except external users grupe isto trazi ako je rijec o User tipu koji nije external					
			    END
		    ELSE
			    BEGIN
				    INSERT INTO @OrganisationDescendant([DisplayName],[PrincipalID],[ContextID],[PrincipalTypeID],[PathLevel],[AccountEnabled])
					    SELECT Principals.DisplayName, Principals.PrincipalID, org.ContextID AS ContextId, Principals.PrincipalTypeID, @Depth, LoadedPrincipals.AccountEnabled
					    FROM PrincipalMemberships 
					    JOIN @OrganisationDescendant AS org ON PrincipalMemberships.UserID = org.PrincipalID AND PrincipalMemberships.LoadingInfoID = @Loc_LoadingInfoID AND org.PathLevel = (@Depth-1)
					    JOIN Principals ON PrincipalMemberships.GroupID = Principals.PrincipalID 		
                        JOIN LoadedPrincipals ON LoadedPrincipals.PrincipalID = Principals.PrincipalID AND LoadedPrincipals.LoadingInfoID = @Loc_LoadingInfoID			
					    WHERE NOT EXISTS(SELECT * FROM @OrganisationDescendant AS s WHERE s.PrincipalID = Principals.PrincipalID AND s.ContextId = org.ContextId )   			
		    END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END
													                                         

        SELECT DISTINCT ScopeID, 
			RoleAssignments.SiteID AS SiteCollectionID, 
			Roles.RoleID, 
			Roles.Title AS RoleTitle, 
			org.[DisplayName],
			org.[PrincipalID], 
			org.[PrincipalTypeID],
			org.[AccountEnabled]		
		FROM @OrganisationDescendant AS org		
		JOIN RoleAssignments ON RoleAssignments.PrincipalID = org.PrincipalID AND RoleAssignments.LoadingInfoID = @Loc_LoadingInfoID                       
        JOIN Roles ON RoleAssignments.RoleID=Roles.RoleID 
		WHERE  EXISTS (SELECT 1 FROM @Loc_SiteCollectionIDs sc WHERE sc.ID = RoleAssignments.SiteID)
        	   AND (NOT EXISTS (SELECT 1 FROM @Loc_RoleIDs) OR EXISTS ( SELECT 1 FROM @Loc_RoleIDs r WHERE r.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'Creating [dbo].[MarkFarmForDeletion]'
GO
CREATE PROCEDURE [dbo].[MarkFarmForDeletion]
	@FarmID int	
	AS
	
	SET NOCOUNT ON	

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

	SELECT @Loc_FarmID = @FarmID
		   	
	UPDATE LoadingInfos 
		SET MarkedForDeletion = 1
	WHERE FarmID = @Loc_FarmID

	UPDATE Farms 
		SET MarkedForDeletion = 1
	WHERE ID = @Loc_FarmID
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 [EventCollection].[v_SPDocKitUserFarms]'
GO
ALTER VIEW [EventCollection].[v_SPDocKitUserFarms]
WITH SCHEMABINDING
AS	
WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT CAST(case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END AS BIT)
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
RoleFarms AS (select  u.LoginName, u.PrincipalTypeID,  f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
			FROM [dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			INNER JOIN [dbo].[SPDocKitRoleFarms] rf ON rf.RoleID = r.ID
			INNER JOIN [dbo].[Farms] f ON rf.FarmID = f.ID
			UNION 
			SELECT u.LoginName, u.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
			FROM[dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			CROSS APPLY [dbo].[Farms] AS f
			WHERE r.Name ='Admins'),
AllFarms AS (
 SELECT NULL AS 'LoginName', NULL AS 'PrincipalTypeID',  f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
		FROM [dbo].[Farms] AS f)

SELECT  f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
FROM(
	SELECT f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
					  , s.RBSecurityEnabled AS 'ResFlag' FROM RoleFarms AS f CROSS APPLY SecurityEnabled as s
	UNION 
	SELECT f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
					  , ~s.RBSecurityEnabled AS 'ResFlag' FROM AllFarms AS f CROSS APPLY SecurityEnabled as s
	)AS f 
WHERE ResFlag = 1
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_Farms]'
GO
ALTER VIEW [dbo].[v_Farms]
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     
      ),
	  MyFarms
	  AS
	  (
	        SELECT DISTINCT a.FarmID
			FROM [dbo].[v_SPObjectsWithAccess] a
	  ),
	 MySPDocKitRoleFarms 
	 AS
	 (
			SELECT DISTINCT rf.FarmID 
			FROM [dbo].[SPDocKitRoleFarms] rf
			JOIN [dbo].[v_MySPDocKitRoles] r ON r.ID = rf.RoleID
	 )
	SELECT [ID]
		  ,[FarmGuid]
		  ,[Name]
		  ,[LSE]
		  ,[LEE]
		  ,[LicenseValidTo]
		  ,[Hash]
		  ,[CentralAdministrationUrl]
		  ,[Version]
		  ,[UsageDatabaseConnectionString]
		  ,[DiagnosticsLogLocation]
		  ,[FarmType]
	  FROM SecurityEnabled sec,  [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[Farms] f
	  WHERE f.MarkedForDeletion = 0 AND 
	  (sec.RBSecurityEnabled = 0 OR 
		acc.HasAccess = 1 OR 
		f.ID IN (SELECT a.FarmID FROM MyFarms a) 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].[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]
	  ,f.[FarmType]
	  ,l.[LoadDuration]
	  ,l.[MarkedForDeletion]
	  ,l.[InProgress]
  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'Creating index [IX_WebAppID_SPGuid] on [dbo].[SharePointObjects]'
GO
CREATE NONCLUSTERED INDEX [IX_WebAppID_SPGuid] ON [dbo].[SharePointObjects] ([WebAppID], [SPGuid]) WHERE ([SPObjectTypeID]=(1))
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 = '7.2.1' 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
UPDATE Options SET OptionValue = '[7.0,7.2)' WHERE OptionID = 1003
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

