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
PRINT N'Creating role spdockit_monitoring_viewer'
GO
IF DATABASE_PRINCIPAL_ID('spdockit_monitoring_viewer') IS NULL
	CREATE ROLE [spdockit_monitoring_viewer]
AUTHORIZATION [dbo]
GO
PRINT N'Creating role spdockit_report_viewer'
GO
IF DATABASE_PRINCIPAL_ID('spdockit_report_viewer') IS NULL
	CREATE ROLE [spdockit_report_viewer]
AUTHORIZATION [dbo]
GO
PRINT N'Creating role spdockit_snapshot_viewer'
GO
IF DATABASE_PRINCIPAL_ID('spdockit_snapshot_viewer') IS NULL
	CREATE ROLE [spdockit_snapshot_viewer]
AUTHORIZATION [dbo]
GO
BEGIN TRANSACTION
GO
PRINT N'Delete duplicate rows from [dbo].[ScopeAssignments]'
DELETE sc
FROM [dbo].[ScopeAssignments] sc
		LEFT OUTER JOIN (
		   SELECT MIN(ScopeID) as RowId, SPObjectID, LoadingInfoID 
		   FROM [dbo].[ScopeAssignments] 
		   GROUP BY LoadingInfoID, SPObjectID
		) as KeepRows 
		ON sc.ScopeID = KeepRows.RowId AND sc.LoadingInfoID = KeepRows.LoadingInfoID AND KeepRows.SPObjectID = sc.SPObjectID
WHERE KeepRows.RowId IS NULL
GO
PRINT N'Dropping foreign keys from referencing [dbo].[Principals]'
ALTER TABLE [dbo].[PrincipalMemberships] DROP CONSTRAINT [FK_PrincipalMemberships_Principals]
GO
ALTER TABLE [dbo].[PrincipalMemberships] DROP CONSTRAINT [FK_PrincipalMemberships_Principals1]
GO
ALTER TABLE [dbo].[RoleAssignments] DROP CONSTRAINT [FK_RoleAssignments_Principals]
GO
ALTER TABLE [dbo].[SharePointObjects] DROP CONSTRAINT [FK_SharePointObjects_Principals]
GO
ALTER TABLE [dbo].[SharePointObjects] DROP CONSTRAINT [FK_SharePointObjects_Principals1]
GO
ALTER TABLE [dbo].[LoadedPrincipals] DROP CONSTRAINT [FK_LoadedPrincipals_LoadingInfos]
GO
ALTER TABLE [dbo].[SiteCollectionAdministrators] DROP CONSTRAINT [FK_SiteCollectionAdministrators_Principals]
GO
ALTER TABLE [dbo].[WebApplicationPermissions] DROP CONSTRAINT [FK_WebApplicationPermissions_Principals]
GO
PRINT N'Reset SharePointObject references to Principals'
UPDATE [dbo].[SharePointObjects]
	SET [CreatedBy] = NULL
      ,[ModifiedBy] = NULL
PRINT N'Creating temporary indexes'
CREATE NONCLUSTERED INDEX [tempIndex1]
ON [dbo].[PrincipalMemberships] ([GroupID],[LoadingInfoID])
INCLUDE ([UserID])
GO
CREATE NONCLUSTERED INDEX [tempIndex2]
ON [dbo].[PrincipalMemberships] ([UserID],[LoadingInfoID])
INCLUDE ([GroupID])
GO
CREATE NONCLUSTERED INDEX [tempIndex3]
ON [dbo].[RoleAssignments] ([PrincipalID])
INCLUDE ([LoadingInfoID],[ScopeID],[RoleID])
GO
CREATE NONCLUSTERED INDEX [tempIndex4]
ON [dbo].[LoadedPrincipals] ([PrincipalID])
GO
PRINT N'Delete duplicate principals from [dbo].[Principals]'
IF OBJECT_ID('tempdb..#IdsToDelete') IS NOT NULL
    DROP TABLE #IdsToDelete

CREATE TABLE #IdsToDelete (
 DuplicateID int,
 OriginalID int
) 

INSERT INTO #IdsToDelete
SELECT p.PrincipalID, p.PrincipalID
FROM [dbo].[Principals] p
		LEFT OUTER JOIN (
		   SELECT MIN(PrincipalID) as RowId, FarmID, SiteID, Name 
		   FROM [dbo].[Principals] 
		   GROUP BY FarmID, SiteID, Name
		) as KeepRows 
		ON p.PrincipalID = KeepRows.RowId
WHERE KeepRows.RowId IS NULL


UPDATE ids
SET OriginalID = (SELECT MIN(PrincipalID) 
				  FROM [dbo].[Principals] 
				  WHERE FarmID = p.FarmID AND Name = p.Name 
					AND ( (SiteID IS NULL AND p.SiteID IS NULL) OR (SiteID = p.SiteID))
				  GROUP BY FarmID, SiteID, Name
				 )
FROM #IdsToDelete ids		
INNER JOIN [dbo].[Principals] p ON p.PrincipalID = ids.DuplicateID

PRINT N'Merge rows from [dbo].[PrincipalMemberships] for GroupID'
UPDATE pm
SET pm.GroupID = d.OriginalID
FROM [dbo].[PrincipalMemberships] pm		
INNER JOIN #IdsToDelete d ON pm.GroupID = d.DuplicateID AND pm.LoadingInfoID > 0
WHERE NOT EXISTS (SELECT 1 FROM [dbo].[PrincipalMemberships] o WHERE o.LoadingInfoID = pm.LoadingInfoID AND o.GroupID = d.OriginalID AND o.UserID = pm.UserID)

PRINT N'Merge rows from [dbo].[PrincipalMemberships] for UserID'
UPDATE pm
SET pm.UserID = d.OriginalID
FROM [dbo].[PrincipalMemberships] pm		
INNER JOIN #IdsToDelete d ON pm.UserID = d.DuplicateID AND pm.LoadingInfoID > 0
WHERE NOT EXISTS (SELECT 1 FROM [dbo].[PrincipalMemberships] o WHERE o.LoadingInfoID = pm.LoadingInfoID AND o.GroupID = pm.GroupID AND o.UserID = d.OriginalID)

PRINT N'Delete rows from [dbo].[PrincipalMemberships]'
DELETE pm
FROM [dbo].[PrincipalMemberships] pm		
WHERE EXISTS (SELECT * FROM #IdsToDelete ids where ids.DuplicateID = pm.GroupID)

DELETE pm
FROM [dbo].[PrincipalMemberships] pm		
WHERE EXISTS (SELECT * FROM #IdsToDelete ids where ids.DuplicateID = pm.UserID)

PRINT N'Merge rows from [dbo].[SiteCollectionAdministrators] for PrincipalID'
UPDATE sca
SET sca.PrincipalID = d.OriginalID
FROM [dbo].[SiteCollectionAdministrators] sca		
INNER JOIN #IdsToDelete d ON sca.PrincipalID = d.DuplicateID
WHERE NOT EXISTS (SELECT 1 FROM [dbo].[SiteCollectionAdministrators] o WHERE o.LoadingInfoID = sca.LoadingInfoID AND o.SharePointObjectID = sca.SharePointObjectID 
	AND o.IsPrimary = sca.IsPrimary AND o.PrincipalID = d.OriginalID)

PRINT N'Delete rows from [dbo].[SiteCollectionAdministrators]'
DELETE sca
FROM [dbo].[SiteCollectionAdministrators] sca		
WHERE EXISTS (SELECT * FROM #IdsToDelete ids where ids.DuplicateID = sca.PrincipalID)

PRINT N'Merge rows from [dbo].[RoleAssignments] for PrincipalID'
UPDATE ra
SET ra.PrincipalID = d.OriginalID
FROM [dbo].[RoleAssignments] ra		
INNER JOIN #IdsToDelete d ON ra.PrincipalID = d.DuplicateID
WHERE NOT EXISTS (SELECT 1 FROM [dbo].[RoleAssignments] o WHERE o.LoadingInfoID = ra.LoadingInfoID AND o.ScopeID = ra.ScopeID 
	AND o.RoleID = ra.RoleID AND o.PrincipalID = d.OriginalID)

PRINT N'Delete rows from [dbo].[RoleAssignments]'
DELETE ra
FROM [dbo].[RoleAssignments] ra		
WHERE EXISTS (SELECT * FROM #IdsToDelete ids where ids.DuplicateID = ra.PrincipalID)

PRINT N'Merge rows from [dbo].[WebApplicationPermissions] for PrincipalID'
UPDATE wa
SET wa.PrincipalID = d.OriginalID
FROM [dbo].[WebApplicationPermissions] wa		
INNER JOIN #IdsToDelete d ON wa.PrincipalID = d.DuplicateID
WHERE NOT EXISTS (SELECT 1 FROM [dbo].[WebApplicationPermissions] o WHERE o.LoadingInfoID = wa.LoadingInfoID AND o.WebApplicationID = wa.WebApplicationID 
	AND o.ZoneID = wa.ZoneID AND o.PolicyLevelID = wa.PolicyLevelID AND o.PrincipalID = d.OriginalID)

PRINT N'Delete rows from [dbo].[WebApplicationPermissions]'
DELETE wp
FROM [dbo].[WebApplicationPermissions] wp		
WHERE EXISTS (SELECT * FROM #IdsToDelete ids where ids.DuplicateID = wp.PrincipalID)

PRINT N'Merge rows from [dbo].[LoadedPrincipals] for PrincipalID'
UPDATE lp
SET lp.PrincipalID = d.OriginalID
FROM [dbo].[LoadedPrincipals] lp		
INNER JOIN #IdsToDelete d ON lp.PrincipalID = d.DuplicateID
WHERE NOT EXISTS (SELECT 1 FROM [dbo].[LoadedPrincipals] o WHERE o.LoadingInfoID = lp.LoadingInfoID AND o.PrincipalID = d.OriginalID)

PRINT N'Delete rows from [dbo].[LoadedPrincipals]'
DELETE lp
FROM [dbo].[LoadedPrincipals] lp		
WHERE EXISTS (SELECT * FROM #IdsToDelete ids where ids.DuplicateID = lp.PrincipalID)

PRINT N'Delete rows from [dbo].[Principals]'
DELETE p
FROM [dbo].[Principals] p
WHERE EXISTS (SELECT * FROM #IdsToDelete ids where ids.DuplicateID = p.PrincipalID)
GO
PRINT N'Droping temporary indexes'
DROP INDEX [tempIndex1] ON [dbo].[PrincipalMemberships]
GO
DROP INDEX [tempIndex2] ON [dbo].[PrincipalMemberships]
GO
DROP INDEX [tempIndex3] ON [dbo].[RoleAssignments]
GO
DROP INDEX [tempIndex4] ON [dbo].[LoadedPrincipals]
GO
PRINT N'Recreating foreign keys from referencing [dbo].[Principals]'
ALTER TABLE [dbo].[PrincipalMemberships]  WITH CHECK ADD  CONSTRAINT [FK_PrincipalMemberships_Principals] FOREIGN KEY([GroupID])
REFERENCES [dbo].[Principals] ([PrincipalID])
GO
ALTER TABLE [dbo].[PrincipalMemberships] CHECK CONSTRAINT [FK_PrincipalMemberships_Principals]
GO
ALTER TABLE [dbo].[PrincipalMemberships]  WITH CHECK ADD  CONSTRAINT [FK_PrincipalMemberships_Principals1] FOREIGN KEY([GroupID])
REFERENCES [dbo].[Principals] ([PrincipalID])
GO
ALTER TABLE [dbo].[PrincipalMemberships] CHECK CONSTRAINT [FK_PrincipalMemberships_Principals1]
GO
ALTER TABLE [dbo].[RoleAssignments]  WITH CHECK ADD  CONSTRAINT [FK_RoleAssignments_Principals] FOREIGN KEY([PrincipalID])
REFERENCES [dbo].[Principals] ([PrincipalID])
GO
ALTER TABLE [dbo].[RoleAssignments] CHECK CONSTRAINT [FK_RoleAssignments_Principals]
GO
ALTER TABLE [dbo].[SharePointObjects]  WITH CHECK ADD  CONSTRAINT [FK_SharePointObjects_Principals] FOREIGN KEY([CreatedBy])
REFERENCES [dbo].[Principals] ([PrincipalID])
GO
ALTER TABLE [dbo].[SharePointObjects] CHECK CONSTRAINT [FK_SharePointObjects_Principals]
GO
ALTER TABLE [dbo].[SharePointObjects]  WITH CHECK ADD  CONSTRAINT [FK_SharePointObjects_Principals1] FOREIGN KEY([ModifiedBy])
REFERENCES [dbo].[Principals] ([PrincipalID])
GO
ALTER TABLE [dbo].[SharePointObjects] CHECK CONSTRAINT [FK_SharePointObjects_Principals1]
GO
ALTER TABLE [dbo].[LoadedPrincipals]  WITH CHECK ADD  CONSTRAINT [FK_LoadedPrincipals_LoadingInfos] FOREIGN KEY([LoadingInfoID])
REFERENCES [dbo].[LoadingInfos] ([ID])
GO
ALTER TABLE [dbo].[LoadedPrincipals] CHECK CONSTRAINT [FK_LoadedPrincipals_LoadingInfos]
GO
ALTER TABLE [dbo].[SiteCollectionAdministrators]  WITH CHECK ADD  CONSTRAINT [FK_SiteCollectionAdministrators_Principals] FOREIGN KEY([PrincipalID])
REFERENCES [dbo].[Principals] ([PrincipalID])
GO
ALTER TABLE [dbo].[SiteCollectionAdministrators] CHECK CONSTRAINT [FK_SiteCollectionAdministrators_Principals]
GO
ALTER TABLE [dbo].[WebApplicationPermissions]  WITH CHECK ADD  CONSTRAINT [FK_WebApplicationPermissions_Principals] FOREIGN KEY([PrincipalID])
REFERENCES [dbo].[Principals] ([PrincipalID])
GO
ALTER TABLE [dbo].[WebApplicationPermissions] CHECK CONSTRAINT [FK_WebApplicationPermissions_Principals]
GO
PRINT N'Dropping foreign keys from [dbo].[PrincipalMemberships]'
GO
ALTER TABLE [dbo].[PrincipalMemberships] DROP CONSTRAINT[FK_PrincipalMemberships_Principals]
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 foreign keys from [dbo].[RoleAssignments]'
GO
ALTER TABLE [dbo].[RoleAssignments] DROP CONSTRAINT[FK_RoleAssignments_Principals]
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 foreign keys from [dbo].[ScopeAssignments]'
GO
ALTER TABLE [dbo].[ScopeAssignments] DROP CONSTRAINT[FK_ScopeAssignments_LoadingInfo]
ALTER TABLE [dbo].[ScopeAssignments] DROP CONSTRAINT[FK_ScopeAssignments_SharePointObjects]
ALTER TABLE [dbo].[ScopeAssignments] DROP CONSTRAINT[FK_ScopeAssignments_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'Dropping constraints from [dbo].[ScopeAssignments]'
GO
ALTER TABLE [dbo].[ScopeAssignments] DROP CONSTRAINT [PK_ScopeAssignments]
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 index [IX_Principals] from [dbo].[Principals]'
GO
DROP INDEX [IX_Principals] ON [dbo].[Principals]
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].[InsertOrUpdateSharePointObjects]'
GO
DROP PROCEDURE [dbo].[InsertOrUpdateSharePointObjects]
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 [dbo].[SharePointObjectType]
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 [dbo].[FilterIDsType] AS TABLE
(
[ID] [int] NOT 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
CREATE TYPE [dbo].[SharePointObjectType] AS TABLE
(
[Name] [nvarchar] (255) NOT NULL,
[SPObjectTypeID] [int] NOT NULL,
[SPGuid] [uniqueidentifier] NOT NULL,
[ParentID] [int] NOT NULL,
[ParentScopeID] [int] NOT NULL,
[IsRootWeb] [bit] NOT NULL,
[HasUniqueRoleAssignments] [bit] NOT NULL,
[Url] [nvarchar] (2048) NOT NULL,
[ContentTypeID] [int] NULL,
[LastUpdated] [datetime] NULL,
[CreatedOn] [datetime] NULL,
[CreatedBy] [int] NULL,
[ModifiedBy] [int] NULL,
[SharePointContentTypeID] [int] NULL,
[ExternalSharingCapability] [int] NULL,
[AnonymousViewLink] [nvarchar] (1024) NULL,
[AnonymousEditLink] [nvarchar] (1024) 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'Creating [dbo].[SPDocKitRoles]'
GO
CREATE TABLE [dbo].[SPDocKitRoles]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (200) NOT NULL,
[Description] [nvarchar] (max) NULL,
[RolePermissions] [xml] NOT NULL,
[SPObjectSharingPolicy] [int] NOT 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'Creating primary key [PK_SPDocKitRoles] on [dbo].[SPDocKitRoles]'
GO
ALTER TABLE [dbo].[SPDocKitRoles] ADD CONSTRAINT [PK_SPDocKitRoles] PRIMARY KEY CLUSTERED  ([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'Creating index [IX_Name] on [dbo].[SPDocKitRoles]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Name] ON [dbo].[SPDocKitRoles] ([Name]) INCLUDE ([ID], [SPObjectSharingPolicy])
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].[SPDocKitUsers]'
GO
CREATE TABLE [dbo].[SPDocKitUsers]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[LoginName] [nvarchar] (255) NOT NULL,
[DisplayName] [nvarchar] (255) NOT NULL,
[PrincipalTypeID] [int] NOT 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'Creating primary key [PK_SPDocKitUsers] on [dbo].[SPDocKitUsers]'
GO
ALTER TABLE [dbo].[SPDocKitUsers] ADD CONSTRAINT [PK_SPDocKitUsers] PRIMARY KEY CLUSTERED  ([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'Creating index [IX_LoginName_PrincipalTypeID] on [dbo].[SPDocKitUsers]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_LoginName_PrincipalTypeID] ON [dbo].[SPDocKitUsers] ([LoginName], [PrincipalTypeID]) INCLUDE ([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'Creating [dbo].[SPDocKitUserRoles]'
GO
CREATE TABLE [dbo].[SPDocKitUserRoles]
(
[UserID] [int] NOT NULL,
[RoleID] [int] NOT 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'Creating primary key [PK_SPDocKitUserRoles] on [dbo].[SPDocKitUserRoles]'
GO
ALTER TABLE [dbo].[SPDocKitUserRoles] ADD CONSTRAINT [PK_SPDocKitUserRoles] PRIMARY KEY CLUSTERED  ([UserID], [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].[v_IsUserAdmin]'
GO
CREATE VIEW [dbo].[v_IsUserAdmin]
WITH SCHEMABINDING
AS 
	SELECT  (CASE 
		WHEN (COUNT(*)  = 0) THEN 0 
		ELSE 1 
		END) as IsAdmin
	FROM	[dbo].[SPDocKitUsers] u
	INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
	INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
	WHERE IS_MEMBER (u.LoginName) = 1 AND r.Name ='Admins'
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].[v_MySPDocKitRoles]'
GO
CREATE VIEW [dbo].[v_MySPDocKitRoles]
WITH SCHEMABINDING
	AS 
SELECT [ID]
      ,[Name]
      ,[Description]
      ,[RolePermissions]
      ,[SPObjectSharingPolicy]
  FROM [dbo].[SPDocKitRoles] r
  WHERE EXISTS (SELECT 1 FROM [dbo].[SPDocKitUsers] u
						INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID 
						WHERE IS_MEMBER (u.LoginName) = 1 AND ur.RoleID = r.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'Creating [dbo].[SPDocKitSiteCollectionAdministrators]'
GO
CREATE TABLE [dbo].[SPDocKitSiteCollectionAdministrators]
(
[PrincipalID] [int] NOT NULL,
[SiteID] [int] NOT NULL,
[WebApplicationID] [int] NOT NULL,
[FarmID] [int] NOT 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'Creating primary key [PK_SPDocKitSiteCollectionAdministrators] on [dbo].[SPDocKitSiteCollectionAdministrators]'
GO
ALTER TABLE [dbo].[SPDocKitSiteCollectionAdministrators] ADD CONSTRAINT [PK_SPDocKitSiteCollectionAdministrators] PRIMARY KEY CLUSTERED  ([FarmID], [WebApplicationID], [SiteID], [PrincipalID])
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].[SPDocKitRoleSiteCollections]'
GO
CREATE TABLE [dbo].[SPDocKitRoleSiteCollections]
(
[RoleID] [int] NOT NULL,
[FarmID] [int] NOT NULL,
[WebApplicationID] [int] NOT NULL,
[SiteCollectionID] [int] NOT 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'Creating primary key [PK_SPDocKitRoleSiteCollections] on [dbo].[SPDocKitRoleSiteCollections]'
GO
ALTER TABLE [dbo].[SPDocKitRoleSiteCollections] ADD CONSTRAINT [PK_SPDocKitRoleSiteCollections] PRIMARY KEY CLUSTERED  ([RoleID], [FarmID], [WebApplicationID], [SiteCollectionID])
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].[SPDocKitRoleFarms]'
GO
CREATE TABLE [dbo].[SPDocKitRoleFarms]
(
[RoleID] [int] NOT NULL,
[FarmID] [int] NOT 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'Creating primary key [PK_SPDocKitRoleFarms] on [dbo].[SPDocKitRoleFarms]'
GO
ALTER TABLE [dbo].[SPDocKitRoleFarms] ADD CONSTRAINT [PK_SPDocKitRoleFarms] PRIMARY KEY CLUSTERED  ([RoleID], [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 [dbo].[Farms]'
GO
ALTER TABLE [dbo].[Farms] ALTER COLUMN [UsageDatabaseConnectionString] [nvarchar] (1024) 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'Creating [dbo].[SiteCollectionProperties]'
GO
CREATE TABLE [dbo].[SiteCollectionProperties]
(
[LoadingInfoID] [int] NOT NULL,
[SiteCollectionID] [int] NOT NULL,
[ExternalSharingCapability] [int] NOT 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'Creating primary key [PK_SiteCollectionProperties] on [dbo].[SiteCollectionProperties]'
GO
ALTER TABLE [dbo].[SiteCollectionProperties] ADD CONSTRAINT [PK_SiteCollectionProperties] PRIMARY KEY CLUSTERED  ([LoadingInfoID], [SiteCollectionID])
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].[IsAllowedToRunSPDocKit]'
GO
CREATE PROCEDURE [dbo].[IsAllowedToRunSPDocKit]
AS
	WITH SecurityEnabled (OptionValue)
      AS
      (
            SELECT OptionValue
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      )
	
	SELECT  (CASE 
		WHEN ((SELECT TOP 1 OptionValue FROM SecurityEnabled) = 'false') THEN 1
		WHEN (COUNT (*) > 0) THEN 1
		ELSE 0
		END) as GrantAccess
	FROM	[dbo].[SPDocKitUsers] u
	INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
	INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
	WHERE IS_MEMBER (u.LoginName) = 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'Creating [dbo].[AnonymousAccessLinks]'
GO
CREATE TABLE [dbo].[AnonymousAccessLinks]
(
[LoadingInfoID] [int] NOT NULL,
[SharePointObjectID] [int] NOT NULL,
[ViewLink] [nvarchar] (1024) NULL,
[EditLink] [nvarchar] (1024) 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'Creating primary key [PK_AnonymousAccessLinks] on [dbo].[AnonymousAccessLinks]'
GO
ALTER TABLE [dbo].[AnonymousAccessLinks] ADD CONSTRAINT [PK_AnonymousAccessLinks] PRIMARY KEY CLUSTERED  ([LoadingInfoID], [SharePointObjectID])
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].[v_HasAccessToAllSPObjects]'
GO
CREATE VIEW [dbo].[v_HasAccessToAllSPObjects]
WITH SCHEMABINDING
AS 
	SELECT  (CASE 
		WHEN (COUNT(*)  = 0) THEN 0 
		ELSE 1 
		END) as HasAccess
	FROM	[dbo].[SPDocKitUsers] u
	INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
	INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
	WHERE IS_MEMBER (u.LoginName) = 1 AND r.SPObjectSharingPolicy = 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].[Principals]'
GO
ALTER TABLE [dbo].[Principals] ALTER COLUMN [Name] [nvarchar] (300) NOT 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
ALTER TABLE [dbo].[Principals] ADD
[DisplayNameHashed] AS (checksum([DisplayName])) PERSISTED
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_FarmID_SiteID_Name] on [dbo].[Principals]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_FarmID_SiteID_Name] ON [dbo].[Principals] ([FarmID], [SiteID], [Name]) INCLUDE ([PrincipalID], [PrincipalTypeID])
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].[GetFlattenedGroupMembers]'
GO
CREATE 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	

	DECLARE @OrganisationDescendant TABLE ( 
		[LoginName][nvarchar](max),
		[DisplayName][nvarchar](max),
		[PrincipalID] INT, 
		[ContextID] INT,
		[ParentLoginName][nvarchar](max),
		[PrincipalTypeID] INT,
		[PathLevel] INT,
		[AccountEnabled] bit,
		[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 @OrganisationDescendant([LoginName], [DisplayName],[PrincipalID], [ParentID], [ContextID], [ParentLoginName], [PrincipalTypeID],[PathLevel],[AccountEnabled])
				    SELECT principals.Name, principals.DisplayName, principals.PrincipalID, NULL, principals.PrincipalID, principals.Name, principals.PrincipalTypeID, 0 AS PathLevel, LoadedPrincipals.AccountEnabled
				    FROM Principals AS principals 
                    INNER JOIN LoadedPrincipals ON LoadedPrincipals.PrincipalID = principals.PrincipalID AND LoadedPrincipals.LoadingInfoID = @Loc_LoadingInfoID
					INNER JOIN @Loc_PrincipalIDs filter ON principals.PrincipalID = filter.ID
			    END
		    ELSE
			    BEGIN
				    INSERT INTO @OrganisationDescendant([LoginName], [DisplayName],[PrincipalID], [ParentID], [ContextID], [ParentLoginName], [PrincipalTypeID],[PathLevel],[AccountEnabled])
					    SELECT Principals.Name, Principals.DisplayName, Principals.PrincipalID, org.PrincipalID, org.ContextID AS ContextId, principals.Name, Principals.PrincipalTypeID, @Depth, LoadedPrincipals.AccountEnabled
					    FROM PrincipalMemberships 
					    JOIN @OrganisationDescendant AS org on PrincipalMemberships.GroupID = org.PrincipalID and PrincipalMemberships.LoadingInfoID = @Loc_LoadingInfoID and org.PathLevel = (@Depth-1)
					    JOIN Principals ON PrincipalMemberships.UserID = Principals.PrincipalID 			
                        JOIN LoadedPrincipals ON LoadedPrincipals.PrincipalID = Principals.PrincipalID AND LoadedPrincipals.LoadingInfoID = @Loc_LoadingInfoID	
                        WHERE org.ContextID <> Principals.PrincipalID AND org.ParentLoginName <> 'c:0(.s|true' AND org.ParentLoginName <> 'rolemanager|spo-grid-all-users'
		    END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END

		SELECT	                                                    
			o.PrincipalID,
			o.PrincipalTypeID,
			o.DisplayName,
			o.AccountEnabled,
			o.ParentID,
			o.ContextID as RootParentID
        FROM @OrganisationDescendant o 
        WHERE (NOT EXISTS (SELECT 1 FROM @PrincipalTypeIDs) OR EXISTS ( SELECT 1 FROM @PrincipalTypeIDs pt WHERE pt.ID = o.PrincipalTypeID))
		AND (NOT EXISTS (SELECT 1 FROM @PrincipalStatusIDs) OR EXISTS ( SELECT 1 FROM @PrincipalStatusIDs ps WHERE ps.ID = o.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'Creating [dbo].[GetFlattenedRoleAssignmentsForScopeIds]'
GO
CREATE 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
	
	DECLARE @OrganisationDescendant TABLE ( 
                    [LoginName][nvarchar](max),
                    [DisplayName][nvarchar](max),
                    [PrincipalID] INT, 
                    [ContextID] INT,
                    [PrincipalTypeID] INT,
                    [PathLevel] INT,
                    [AccountEnabled] bit
                    )

    DECLARE @Principals TABLE (                                       
                        [PrincipalID] INT                                      
                    )    
    

    INSERT INTO @Principals([PrincipalID])
		    SELECT 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 @OrganisationDescendant([LoginName],[DisplayName],[PrincipalID],[ContextID], [PrincipalTypeID],[PathLevel],[AccountEnabled])
				    SELECT principals.Name, principals.DisplayName, principals.PrincipalID, principals.PrincipalID, principals.PrincipalTypeID, 0 AS PathLevel, LoadedPrincipals.AccountEnabled
				    FROM Principals AS principals 
                    JOIN LoadedPrincipals ON LoadedPrincipals.PrincipalID = principals.PrincipalID AND LoadedPrincipals.LoadingInfoID = @Loc_LoadingInfoID
                    WHERE EXISTS (SELECT * FROM @Principals as p WHERE p.PrincipalID=principals.PrincipalID) 
			    END
		    ELSE
			    BEGIN
				    INSERT INTO @OrganisationDescendant([LoginName],[DisplayName],[PrincipalID],[ContextID], [PrincipalTypeID],[PathLevel],[AccountEnabled])
					    SELECT principals.Name, Principals.DisplayName, Principals.PrincipalID, org.ContextID AS ContextId, Principals.PrincipalTypeID, @Depth, LoadedPrincipals.AccountEnabled
					    FROM PrincipalMemberships 
					    JOIN @OrganisationDescendant AS org on PrincipalMemberships.GroupID = org.PrincipalID and PrincipalMemberships.LoadingInfoID = @Loc_LoadingInfoID and org.PathLevel = (@Depth-1)
					    JOIN Principals ON PrincipalMemberships.UserID = Principals.PrincipalID 			
                        JOIN LoadedPrincipals ON LoadedPrincipals.PrincipalID = Principals.PrincipalID AND LoadedPrincipals.LoadingInfoID = @Loc_LoadingInfoID
                        WHERE org.ContextID <> Principals.PrincipalID AND org.LoginName <> 'c:0(.s|true' AND org.LoginName <> 'rolemanager|spo-grid-all-users'
		    END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END
        SELECT	
        s.ID as ScopeID,
        Roles.Title AS Title,
        o.DisplayName As PrincipalName,
		o.LoginName as PrincipalUsername,
        PrincipalTypes.Name As Type,
        Roles.RoleID AS [ID],
        o.PrincipalID AS PrincipalID,                                                
        o.PrincipalTypeID as PrincipalTypeID,
        o.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
        FROM @Loc_Scopes as s
        JOIN RoleAssignments ON RoleAssignments.ScopeID = s.ID AND RoleAssignments.LoadingInfoID = @Loc_LoadingInfoID
        Join @OrganisationDescendant as o on o.ContextID=RoleAssignments.PrincipalID
        JOIN PrincipalTypes ON o.PrincipalTypeID=PrincipalTypes.PrincipalTypeID
        JOIN Principals ON o.ContextID = Principals.PrincipalID
        JOIN Roles ON RoleAssignments.RoleID=Roles.RoleID
                                                                            
        WHERE (NOT EXISTS (SELECT 1 FROM @Loc_PrincipalTypeIDs) OR EXISTS ( SELECT 1 FROM @Loc_PrincipalTypeIDs pt WHERE pt.ID = o.PrincipalTypeID))
			  AND (NOT EXISTS (SELECT 1 FROM @Loc_PrincipalStatusIDs) OR EXISTS ( SELECT 1 FROM @Loc_PrincipalStatusIDs ps WHERE ps.ID = o.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'Creating [dbo].[GetRoleAssignmentsForScopeIDs]'
GO
CREATE 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
    FROM @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'Creating [dbo].[GetUserPermissionAssignments]'
GO
CREATE 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                                                    
			    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'Altering [dbo].[InsertOrUpdatePrincipal]'
GO
ALTER PROCEDURE [dbo].[InsertOrUpdatePrincipal]
	@PrincipalID int = NULL OUTPUT ,
	@Name nvarchar(MAX),
	@PrincipalTypeID int,
	@SiteID int = NULL,
	@TenantID int = NULL,
	@DisplayName nvarchar(MAX)=NULL,
	@Email nvarchar(MAX)=NULL,
	@AccountEnabled bit,
	@ExternalUser bit,
	@FarmID int,
	@LoadingInfoID int
AS
BEGIN
	SET NOCOUNT ON	
	IF @DisplayName IS NULL OR @DisplayName = '' 
		SET @DisplayName=@Name

	IF @PrincipalID IS NULL	
	BEGIN
		IF @SiteID IS NULL	
		BEGIN
		  SELECT @PrincipalID = p.PrincipalID
		  FROM [dbo].[Principals] p
		  WHERE p.FarmID = @FarmID AND p.Name = @Name AND p.SiteID IS NULL
		END
		ELSE
		BEGIN
		  SELECT @PrincipalID = p.PrincipalID
		  FROM [dbo].[Principals] p
		  WHERE p.FarmID = @FarmID AND p.Name = @Name AND p.SiteID = @SiteID		 
		END
	END
	
	IF @PrincipalID IS NULL	
	BEGIN
		INSERT INTO [dbo].[Principals]
           ([Name]
           ,[PrincipalTypeID]
           ,[SiteID]
           ,[TenantID]
           ,[DisplayName]
           ,[Email]           
           ,[ExternalUser]
           ,[FarmID])
		VALUES(@Name
			,@PrincipalTypeID
			,@SiteID
			,@TenantID
			,@DisplayName
			,@Email			
			,@ExternalUser
			,@FarmID)
		SET @PrincipalID=SCOPE_IDENTITY()
	END
	ELSE
	BEGIN
		UPDATE [dbo].[Principals] SET DisplayName = @DisplayName, Email =@Email WHERE PrincipalID = @PrincipalID		
	END

	INSERT INTO LoadedPrincipals(PrincipalID, LoadingInfoID, AccountEnabled) 
	SELECT  @PrincipalID, @LoadingInfoID, @AccountEnabled
	WHERE NOT EXISTS ( SELECT 1 
						FROM LoadedPrincipals 
						WHERE LoadedPrincipals.LoadingInfoID = @LoadingInfoID AND LoadedPrincipals.PrincipalID = @PrincipalID)
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].[InsertOrUpdateSharePointObject]'
GO
ALTER PROCEDURE [dbo].[InsertOrUpdateSharePointObject]
	@ID INT=NULL,
	@Name nvarchar(255),
	@ObjectTypeID INT,
	@SPSiteGuid uniqueidentifier,
	@SPGuid uniqueidentifier,
	@IsRootWeb BIT,
	@webAppID INT,
	@HasUniqueRoleAssignments BIT,
	@Url nvarchar(2048),
	@SiteID INT,
	@BaseTemplateTypeID INT=NULL,
	@LastUpdated datetime = NULL,
	@CreatedOn datetime = NULL,
	@CreatedBy INT=NULL,
	@ModifiedBy INT=NULL,
	@SiteSize BIGINT=NULL,
	@MaxQuota BIGINT=NULL,
	@FarmID INT,
	@LoadingInfoID INT,
	@ParentID INT=NULL,
	@CreateScopeAssignments bit,
	@ParentPermissionScopeID INT=NULL,
	@ForceCreateNewPermissionScope bit,
	@ExternalSharingCapability INT = NULL,
	@spObjectID INT OUTPUT,
	@actualPermissionScopeID INT=NULL OUTPUT
	AS
	BEGIN
		SET NOCOUNT ON
	  	
		IF @ID IS NULL
		BEGIN
			INSERT INTO [dbo].[SharePointObjects]  ([Name]
							,[SPObjectTypeID]
							,[SPSiteGuid]
							,[SPGuid]
							,[IsRootWeb]
							,[WebAppID]
							,[HasUniqueRoleAssignments]
							,[Url]
							,[SiteID]
							,[ContentTypeID]
							,[LastUpdated]
							,[CreatedOn]
							,[CreatedBy]
							,[ModifiedBy]
							,[SiteSize]
							,[MaxQuota]
							,[FarmID])
				VALUES(@Name,
				@ObjectTypeID
				,@SPSiteGuid
				,@SPGuid
				,@IsRootWeb
				,@webAppID
				,@HasUniqueRoleAssignments
				,@Url
				,@SiteID
				,@BaseTemplateTypeID
				,@LastUpdated
				,@CreatedOn
				,@CreatedBy
				,@ModifiedBy
				,@SiteSize
				,@MaxQuota
				,@FarmID)
				SET @spObjectID = SCOPE_IDENTITY()
				IF @ObjectTypeID = 1
					UPDATE [dbo].[SharePointObjects] SET SiteID=@spObjectID WHERE ID = @spObjectID
		END
		ELSE
		BEGIN
			IF @ObjectTypeID = 1
				SET @SiteID = @ID
			
			UPDATE [dbo].[SharePointObjects]
			SET [Name] = @Name
			,[SPObjectTypeID] =@ObjectTypeID
			,[SPSiteGuid] = @SPSiteGuid
			,[SPGuid] = @SPGuid
			,[IsRootWeb] = @IsRootWeb
			,[WebAppID] = @webAppID
			,[HasUniqueRoleAssignments] = @HasUniqueRoleAssignments
			,[Url] = @Url
			,[SiteID] =@SiteID
			,[ContentTypeID] = @BaseTemplateTypeID
			,[LastUpdated] = @LastUpdated
			,[CreatedOn] = @CreatedOn
			,[CreatedBy] = @CreatedBy
			,[ModifiedBy] =@ModifiedBy
			,[SiteSize] = @SiteSize
			,[MaxQuota] =@MaxQuota
			,[FarmID] = @FarmID
				WHERE ID=@ID
				SET @spObjectID = @ID
		END
			
		IF @ObjectTypeID = 1
			INSERT INTO [dbo].[LoadedSiteCollections] VALUES(@LoadingInfoID, @WebAppID, @spObjectID)
		if @ParentID IS NOT NULL
			INSERT INTO [dbo].[SharePointObjectMemberships] VALUES(@LoadingInfoID, @ParentID, @spObjectID)
		
		IF @CreateScopeAssignments = 1
		BEGIN
			IF @ForceCreateNewPermissionScope = 1 OR @ParentPermissionScopeID IS NULL
			BEGIN
				INSERT INTO [dbo].[PermissionScopes] DEFAULT VALUES
				SET @actualPermissionScopeID = SCOPE_IDENTITY()
			END
		ELSE
			SET @actualPermissionScopeID =  @ParentPermissionScopeID
			INSERT INTO [dbo].[ScopeAssignments] VALUES (@LoadingInfoID, @actualPermissionScopeID, @spObjectID)
		END

		if @ExternalSharingCapability IS NOT NULL
			INSERT INTO [dbo].[SiteCollectionProperties] ([LoadingInfoID], [SiteCollectionID], [ExternalSharingCapability]) VALUES (@LoadingInfoID, @spObjectID, @ExternalSharingCapability)
				
	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'Creating [dbo].[InsertOrUpdateSharePointObjects]'
GO
CREATE PROCEDURE [dbo].[InsertOrUpdateSharePointObjects]
	@LoadingInfoID int,
	@FarmID int,
	@WebAppID int,
	@SiteID int,
	@SPSiteGuid uniqueidentifier,
	@CreateScopeAssignments bit,
	@TableVariable SharePointObjectType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newSpObjects (
		[ID] [int] NULL,
		[Name] nvarchar(255) NOT NULL,
		[SPObjectTypeID] [int] NOT NULL,
		[SPGuid] [uniqueidentifier] NOT NULL,
		[ParentID] [int] NOT NULL,
		[ParentScopeID] [int] NOT NULL,
		[IsRootWeb] [bit] NOT NULL,
		[HasUniqueRoleAssignments] [bit] NOT NULL,
		[Url] nvarchar(2048) NOT NULL,
		[ContentTypeID] [int] NULL,
		[LastUpdated] [datetime] NULL,
		[CreatedOn] [datetime] NULL,
		[CreatedBy] [int] NULL,
		[ModifiedBy] [int] NULL,
		[SharePointContentTypeID] [int] NULL
	) 

	INSERT INTO #newSpObjects ([ID], [Name], [SPObjectTypeID], [SPGuid], [ParentID], [ParentScopeID], [IsRootWeb], [HasUniqueRoleAssignments], [Url], [ContentTypeID], [LastUpdated], [CreatedOn], [CreatedBy], [ModifiedBy], [SharePointContentTypeID])
	SELECT spo.ID,
	    tv.[Name],
		tv.[SPObjectTypeID],
		tv.[SPGuid],
		tv.[ParentID],
		tv.[ParentScopeID],
		tv.[IsRootWeb],
		tv.[HasUniqueRoleAssignments],
		tv.[Url],
		tv.[ContentTypeID],
		tv.[LastUpdated],
		tv.[CreatedOn],
		tv.[CreatedBy],
		tv.[ModifiedBy],
		tv.[SharePointContentTypeID]
	  FROM @TableVariable tv
	  LEFT OUTER JOIN SharePointObjects spo ON spo.SPGuid = tv.SPGuid AND spo.SiteID = @SiteID AND spo.HasUniqueRoleAssignments = tv.HasUniqueRoleAssignments

	INSERT INTO SharePointObjects ([Name], [SPObjectTypeID], [SPSiteGuid], [SPGuid], [IsRootWeb], [WebAppID], [HasUniqueRoleAssignments], [Url], [SiteID]
           ,[ContentTypeID], [LastUpdated], [CreatedOn], [CreatedBy], [ModifiedBy], [SiteSize], [MaxQuota], [FarmID])
	SELECT n.[Name]
		  ,n.[SPObjectTypeID]
		  ,@SPSiteGuid
		  ,n.[SPGuid]
		  ,n.[IsRootWeb]
		  ,@WebAppID	  
		  ,n.[HasUniqueRoleAssignments]
		  ,n.[Url]
		  ,@SiteID
		  ,n.[ContentTypeID]
		  ,n.[LastUpdated]
		  ,n.[CreatedOn]
		  ,n.[CreatedBy]
		  ,n.[ModifiedBy]
		  ,NULL
		  ,NULL
		  ,@FarmID
	FROM #newSpObjects n
	WHERE n.ID IS NULL

	UPDATE spo
		SET [Name] = n.[Name]		
		,[Url] = n.[Url]
		,[ContentTypeID] = n.[ContentTypeID]
		,[LastUpdated] = n.[LastUpdated]
		,[ModifiedBy] = n.[ModifiedBy]
	FROM SharePointObjects spo
	JOIN #newSpObjects n ON n.ID = spo.ID
	WHERE n.ID IS NOT NULL

	UPDATE n
	   SET n.[ID] = spo.[ID] 		  
	FROM #newSpObjects n
	JOIN SharePointObjects spo ON spo.SPGuid = n.SPGuid AND spo.SiteID = @SiteID AND spo.HasUniqueRoleAssignments = n.HasUniqueRoleAssignments
	WHERE n.[ID] IS NULL

	INSERT INTO [dbo].[SharePointObjectMemberships]
           ([LoadingInfoID]
           ,[ParentID]
           ,[ChildID])
	 SELECT @LoadingInfoID, n.ParentID, n.ID
	 FROM #newSpObjects n

	 INSERT INTO [dbo].[SharePointObjectContentType]
           ([LoadingInfoID]
           ,[ContentTypeID]
           ,[SharePointObjectID])
	 SELECT @LoadingInfoID, n.SharePointContentTypeID, n.ID
	 FROM #newSpObjects n
	 WHERE n.SharePointContentTypeID IS NOT NULL

	 IF @CreateScopeAssignments = 1
	 BEGIN
		
		 INSERT INTO [dbo].[ScopeAssignments]
			   ([LoadingInfoID]
			   ,[ScopeID]
			   ,[SPObjectID])
		 SELECT @LoadingInfoID, n.ParentScopeID, n.ID
		 FROM #newSpObjects n
		 WHERE n.HasUniqueRoleAssignments = 0
		 		
		 DECLARE @newScopes TABLE (ScopeID int, SPObjectID int)
		 DECLARE @SpObjectID INT
		 DECLARE @PermissionScopeID INT
		 DECLARE @RowCursor CURSOR
	 
		 SET @RowCursor = CURSOR FOR
		 SELECT n.ID
		 FROM #newSpObjects n
		 WHERE n.HasUniqueRoleAssignments = 1

		 OPEN @RowCursor

		FETCH NEXT FROM @RowCursor INTO @SpObjectID
		WHILE @@FETCH_STATUS = 0
		BEGIN	
			INSERT INTO [dbo].[PermissionScopes] DEFAULT VALUES
			INSERT INTO @newScopes VALUES (SCOPE_IDENTITY(), @SpObjectID)
			FETCH NEXT FROM @RowCursor INTO @SpObjectID	
		END
		CLOSE @RowCursor
		DEALLOCATE @RowCursor
           
		 INSERT INTO [dbo].[ScopeAssignments]
			   ([LoadingInfoID]
			   ,[ScopeID]
			   ,[SPObjectID])
		 SELECT @LoadingInfoID, s.ScopeId, s.SPObjectId
		 FROM @newScopes s 

		SELECT n.SPGuid, n.ID, s.ScopeID
		FROM #newSpObjects n
		INNER JOIN [dbo].[ScopeAssignments] s ON s.LoadingInfoID = @LoadingInfoID AND s.SPObjectID = n.ID
	 END
	 ELSE
	 BEGIN
	 	SELECT n.SPGuid, n.ID, -1 as ScopeID
		FROM #newSpObjects n
	 END
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'Creating [dbo].[UpdatePrincipalsWithDuplicateNames]'
GO
CREATE PROCEDURE [dbo].[UpdatePrincipalsWithDuplicateNames]
	@LoadingInfoID int
AS
BEGIN
    SET NOCOUNT ON	
	
	DECLARE @FarmID int

	SELECT TOP 1 @FarmID = l.FarmID
	FROM [dbo].[LoadingInfos] l
	WHERE l.ID = @LoadingInfoID

	DECLARE @DuplicateSPGroups TABLE ([PrincipalID] INT, [SiteID] INT)
	INSERT INTO @DuplicateSPGroups ([PrincipalID], [SiteID])
	SELECT p1.PrincipalID, p1.SiteID
	FROM [dbo].[Principals] p1
	INNER JOIN (SELECT p2.DisplayNameHashed
				FROM [dbo].[Principals] p2
				WHERE p2.FarmID = @FarmID AND p2.PrincipalTypeID = 1 AND p2.SiteID IS NOT NULL
				GROUP BY p2.DisplayNameHashed
				HAVING COUNT(p2.PrincipalID) > 1) AS duplicates
		  ON p1.DisplayNameHashed = duplicates.DisplayNameHashed
	WHERE p1.FarmID = @FarmID AND p1.PrincipalTypeID = 1 AND p1.SiteID IS NOT NULL

	UPDATE spGroup
	SET DisplayName =  spGroup.DisplayName + ' (' + ( SELECT Name FROM [dbo].[SharePointObjects] spo WHERE spo.ID = d.SiteID) + ')'					
	FROM [dbo].[Principals] spGroup
	INNER JOIN @DuplicateSPGroups d ON d.PrincipalID = spGroup.PrincipalID 
	WHERE spGroup.DisplayName NOT LIKE '%)'

	DECLARE @DuplicateADObjects TABLE ([PrincipalID] INT)
	INSERT INTO @DuplicateADObjects ([PrincipalID])
	SELECT p1.PrincipalID
	FROM [dbo].[Principals] p1
	INNER JOIN (SELECT p2.DisplayNameHashed
				FROM [dbo].[Principals] p2
				WHERE p2.FarmID = @FarmID AND p2.PrincipalTypeID > 1 AND p2.SiteID IS NULL
				GROUP BY p2.DisplayNameHashed
				HAVING COUNT(p2.PrincipalID) > 1) AS duplicates
		  ON p1.DisplayNameHashed = duplicates.DisplayNameHashed
	WHERE p1.FarmID = @FarmID AND p1.PrincipalTypeID > 1 AND p1.SiteID IS NULL

	UPDATE adObject
	SET DisplayName =  adObject.DisplayName + ' (' + adObject.Name +')'					
	FROM [dbo].[Principals] adObject
	INNER JOIN @DuplicateADObjects d ON d.PrincipalID = adObject.PrincipalID 
	WHERE adObject.DisplayName NOT LIKE '%)'
	
	RETURN 0
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'Creating [dbo].[UpdateSPDocKitSiteCollectionAdministrators]'
GO
CREATE PROCEDURE [dbo].[UpdateSPDocKitSiteCollectionAdministrators]
	@LoadingInfoID int
AS
	SET NOCOUNT ON

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

	SELECT @Loc_LoadingInfoID = @LoadingInfoID 



	DECLARE @PermissionsLoaded bit
	DECLARE @FarmID int

	SELECT TOP 1 @PermissionsLoaded = l.PermissionsLoaded,
				@FarmID = l.FarmID
	FROM [dbo].[LoadingInfos] l
	WHERE l.ID = @Loc_LoadingInfoID

	IF @PermissionsLoaded <> 1
	BEGIN
		RETURN -1
	END

	--prvo obrisi stare admine za sve loadane site kolekcije
	DELETE sca FROM [dbo].[SPDocKitSiteCollectionAdministrators] sca
	INNER JOIN [dbo].[LoadedSiteCollections] lsc ON lsc.SiteCollectionID = sca.SiteID AND lsc.WebApplicationID = sca.WebApplicationID AND lsc.LoadingInfoID = @Loc_LoadingInfoID 
	WHERE sca.FarmID = @FarmID


	DECLARE @OrganisationDescendant TABLE ( 
		[PrincipalID] INT, 
		[ContextID] INT, 
		[PathLevel] INT,
		[SiteCollectionID] INT,
		[WebApplicationID] 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 @OrganisationDescendant([PrincipalID], [ContextID], [PathLevel], [SiteCollectionID], [WebApplicationID])
				SELECT pm.UserID, pm.UserID, 0 AS PathLevel, lsc.SiteCollectionID, lsc.WebApplicationID
				FROM [dbo].[LoadedSiteCollections] lsc
				INNER JOIN [dbo].[Principals] p ON p.SiteID = lsc.SiteCollectionID AND p.Name = 'SiteAdministrators' AND p.FarmID = @FarmID AND p.SiteID = lsc.SiteCollectionID
				INNER JOIN [dbo].[PrincipalMemberships] pm ON pm.GroupID = p.PrincipalID AND pm.LoadingInfoID = @Loc_LoadingInfoID	
				WHERE lsc.LoadingInfoID = @Loc_LoadingInfoID		
			END
		ELSE
			BEGIN
				INSERT INTO @OrganisationDescendant([PrincipalID], [ContextID], [PathLevel], [SiteCollectionID], [WebApplicationID])
					SELECT Principals.PrincipalID, org.ContextID, @Depth, org.SiteCollectionID, org.WebApplicationID
					FROM PrincipalMemberships 
					JOIN @OrganisationDescendant 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

	--zapisi tko su novi site collection admini
	INSERT INTO [dbo].[SPDocKitSiteCollectionAdministrators]
           ([PrincipalID]
           ,[SiteID]
           ,[WebApplicationID]
           ,[FarmID])
	SELECT DISTINCT org.PrincipalID,
		   org.SiteCollectionID,
		   org.WebApplicationID,
		   @FarmID	
	FROM @OrganisationDescendant org
	INNER JOIN [dbo].[Principals] p ON org.PrincipalID = p.PrincipalID
	WHERE p.PrincipalTypeID = 2 --zanimaju nas samo useri ne i AD grupe

RETURN 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].[ContentType]'
GO
ALTER TABLE [dbo].[ContentType] ADD
[IsFromContentTypeHub] [bit] NOT NULL CONSTRAINT [Default_IsFromContentTypeHub_Value] 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'Creating [dbo].[v_SPObjectsWithAccess]'
GO
CREATE VIEW [dbo].[v_SPObjectsWithAccess]
WITH SCHEMABINDING
AS 
	SELECT DISTINCT rsc.FarmID,
		   rsc.WebApplicationID,
		   rsc.SiteCollectionID
	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].[SPDocKitRoleSiteCollections] rsc ON r.ID = rsc.RoleID
	WHERE IS_MEMBER (u.LoginName) = 1 AND r.SPObjectSharingPolicy = 3

	-- bitno je da ovo bude DISTINCT
	UNION 

	SELECT DISTINCT sca.FarmID,
		   sca.WebApplicationID,
		   sca.SiteID
	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 JOIN [dbo].[SPDocKitSiteCollectionAdministrators] sca 
	INNER JOIN [dbo].[Principals] p ON sca.PrincipalID = p.PrincipalID
	WHERE IS_MEMBER (u.LoginName) = 1 AND r.SPObjectSharingPolicy = 2 AND p.Name = SUSER_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
PRINT N'Creating [dbo].[v_WebApplicationConfigs]'
GO
CREATE VIEW [dbo].[v_WebApplicationConfigs]
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     
      ),
	  MyWebApplications
	  AS
	  (
	        SELECT DISTINCT a.WebApplicationID
			FROM [dbo].[v_SPObjectsWithAccess] a
	  )
	  SELECT wc.[ID]
		  ,wc.[WebApplicationID]
		  ,wc.[WebApplicationZoneID]
		  ,wc.[ServerID]
		  ,wc.[LoadingInfoID]
		  ,wc.[FileContent]
		  ,s.[Name] as ServerName
		  ,z.[ZoneName] as ZoneName
	  FROM SecurityEnabled sec, [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[WebApplicationConfigs] wc
	  INNER JOIN [dbo].[MonitoringServers] s ON s.ID = wc.ServerID
	  INNER JOIN [dbo].[WebApplicationZones] z ON z.ZoneID = wc.WebApplicationZoneID
	  LEFT OUTER JOIN MyWebApplications a ON a.WebApplicationID = wc.WebApplicationID
	  WHERE sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1 OR a.WebApplicationID IS NOT 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'Creating [dbo].[v_WebApplications]'
GO
CREATE VIEW [dbo].[v_WebApplications]
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     
      ),
	  MyWebApplications
	  AS
	  (
	        SELECT DISTINCT a.WebApplicationID
			FROM [dbo].[v_SPObjectsWithAccess] a
	  )
	  SELECT [ID]
		  ,[Name]
		  ,[SPGuid]
		  ,[Url]
		  ,[Username]
		  ,[Password]
		  ,[Hash]
		  ,[BusinessPlanID]
		  ,[IsPartner]
		  ,[UseOrganizationalAccount]
		  ,w.[FarmID]
	  FROM SecurityEnabled sec, [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[WebApplications] w
	  LEFT OUTER JOIN MyWebApplications a ON a.WebApplicationID = w.ID
	  WHERE sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1 OR a.WebApplicationID IS NOT 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'Creating [dbo].[v_FarmDescriptions]'
GO
CREATE 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]
	  ,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'Creating [dbo].[v_Farms]'
GO
CREATE 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]
		  ,[Hash]
		  ,[CentralAdministrationUrl]
		  ,[Version]
		  ,[UsageDatabaseConnectionString]
	  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'Creating [dbo].[v_Principals]'
GO
CREATE VIEW [dbo].[v_Principals]
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     
      )
	SELECT [PrincipalID]
		  ,[Name]
		  ,[PrincipalTypeID]
		  ,[SiteID]
		  ,[TenantID]
		  ,[DisplayName]
		  ,[Email]
		  ,[ExternalUser]
		  ,p.[FarmID]
	  FROM SecurityEnabled sec, [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[Principals] p
	  LEFT OUTER JOIN [dbo].[v_SPObjectsWithAccess] a ON a.SiteCollectionID = p.SiteID
	  WHERE SiteID IS NULL OR sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1 OR a.FarmID IS NOT 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'Creating [dbo].[v_SharePointObjects]'
GO
CREATE VIEW [dbo].[v_SharePointObjects]
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     
      )
	SELECT spo.[ID]
		  ,spo.[Name]
		  ,[SPObjectTypeID]
		  ,[SPSiteGuid]
		  ,[SPGuid]
		  ,[IsRootWeb]
		  ,[WebAppID]
		  ,[HasUniqueRoleAssignments]
		  ,[Url]
		  ,[SiteID]
		  ,[ContentTypeID]
		  ,[LastUpdated]
		  ,[CreatedOn]
		  ,[CreatedBy]
		  ,[ModifiedBy]
		  ,[SiteSize]
		  ,[MaxQuota]
		  ,spo.[FarmID]
	  FROM SecurityEnabled sec,  [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[SharePointObjects] spo
	  LEFT OUTER JOIN [dbo].[v_SPObjectsWithAccess] a ON a.SiteCollectionID = spo.SiteID
	  WHERE sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1 OR a.WebApplicationID IS NOT 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 [dbo].[GetChildrenForParent]'
GO
ALTER PROCEDURE [dbo].[GetChildrenForParent]
	@LoadingInfoID INT,
	@FarmID INT,
	@ParentID INT
AS
BEGIN 
	SET NOCOUNT ON
	DECLARE @SharePointObjectNodes TABLE
	( 
		[ID] [int],   
		[Url] [nvarchar](255),
		[Name] [nvarchar](255),
		[SPGuid] [uniqueidentifier],
		[HasChildren] [bit],
		[SharePointObjectType] [int],
		[HasUniquePermissions] [bit])

		INSERT INTO @SharePointObjectNodes([ID], [Url], [Name], [SPGuid], [HasChildren], [SharePointObjectType], [HasUniquePermissions])
			SELECT	[SPObject].[ID],
					[SPObject].[Url], 
					[SPObject].[Name],
					[SPObject].[SPGuid],
					(CASE WHEN EXISTS 
						(
						SELECT [dbo].[SharePointObjectMemberships].[ParentID] FROM [dbo].[SharePointObjectMemberships] WHERE [dbo].[SharePointObjectMemberships].[LoadingInfoID] = @LoadingInfoID
						AND [dbo].[SharePointObjectMemberships].[ParentID] = [SPObject].[ID]
						) THEN 1 ELSE 0 END),
					[SPObject].[SPObjectTypeID],
					[SPObject].[HasUniqueRoleAssignments]
			FROM [dbo].[v_SharePointObjects] AS [SPObject]
			JOIN [dbo].[SharePointObjectMemberships] AS [Membership] ON [Membership].[ParentID] = @ParentID AND [Membership].[LoadingInfoID] = @LoadingInfoID AND [Membership].[ChildID] = [SPObject].[ID]
		SELECT * FROM @SharePointObjectNodes ORDER BY [SharePointObjectType], [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'Creating [dbo].[GetContentTypeListUsage]'
GO
CREATE PROCEDURE [dbo].[GetContentTypeListUsage]
	@SiteCollectionID int,
	@ContentTypeID int,
	@LoadingInfoID int
AS
	SET NOCOUNT ON

	SELECT spo.ID
	FROM SharePointObjectContentType spct
	JOIN v_SharePointObjects spo ON spo.ID = spct.SharePointObjectID
	WHERE spct.LoadingInfoID = @LoadingInfoID AND spct.ContentTypeID = @ContentTypeID AND spo.SiteID = @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'Creating [dbo].[GetDetailedPermissions]'
GO
CREATE PROCEDURE [dbo].[GetDetailedPermissions]
	@SpObjectIDs 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_SPObjectIDs 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_SPObjectIDs
	SELECT * FROM @SPObjectIDs
	
	INSERT INTO @Loc_PrincipalTypeIDs
	SELECT * FROM @PrincipalTypeIDs
	
	INSERT INTO @Loc_PermissionTypeIDs
	SELECT * FROM @PermissionTypeIDs	
	
	DECLARE @OrganisationDescendant TABLE ( 
                    [DisplayName][nvarchar](max),
                    [PrincipalID] INT, 
                    [ContextID] INT,
                    [ParentLoginName][nvarchar](max),
                    [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], [ParentLoginName], [PrincipalTypeID],[PathLevel],[AccountEnabled])
				    SELECT principals.DisplayName, principals.PrincipalID, principals.PrincipalID, principals.Name, principals.PrincipalTypeID, 0 AS PathLevel, LoadedPrincipals.AccountEnabled
				    FROM dbo.Principals AS principals 
                    JOIN RoleAssignments ON RoleAssignments.PrincipalID=principals.PrincipalID AND RoleAssignments.LoadingInfoID = @Loc_LoadingInfoID	
					JOIN ScopeAssignments ON RoleAssignments.ScopeID = ScopeAssignments.ScopeID AND ScopeAssignments.LoadingInfoID = @Loc_LoadingInfoID	
					JOIN v_SharePointObjects spo ON ScopeAssignments.SPObjectID = spo.ID 
                    JOIN LoadedPrincipals ON LoadedPrincipals.PrincipalID = principals.PrincipalID AND LoadedPrincipals.LoadingInfoID = @Loc_LoadingInfoID
                    WHERE spo.FarmID = @Loc_FarmID AND EXISTS (SELECT 1 FROM @Loc_SPObjectIDs sp WHERE sp.ID = spo.ID)
			    END
		    ELSE
			    BEGIN
				    INSERT INTO @OrganisationDescendant([DisplayName],[PrincipalID],[ContextID],  [ParentLoginName], [PrincipalTypeID],[PathLevel],[AccountEnabled])
					    SELECT principals.DisplayName, principals.PrincipalID, descendant.ContextID AS ContextId, principals.Name, principals.PrincipalTypeID, @Depth, LoadedPrincipals.AccountEnabled
					    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 			
                        JOIN LoadedPrincipals ON LoadedPrincipals.PrincipalID = principals.PrincipalID AND LoadedPrincipals.LoadingInfoID = @Loc_LoadingInfoID
                        WHERE descendant.ContextID <> principals.PrincipalID AND descendant.ParentLoginName <> 'c:0(.s|true' AND descendant.ParentLoginName <> 'rolemanager|spo-grid-all-users'
                END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END
							
							
		SELECT
            spo.Name as [SharePointObjectName],
            org.PrincipalID AS PrincipalID,
            org.DisplayName As PrincipalName,
            org.PrincipalTypeID as PrincipalTypeID,
            PrincipalTypes.Name As PrincipalType,
            org.AccountEnabled,
            spo.Url,
            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
        FROM v_SharePointObjects spo
        JOIN ScopeAssignments ON ScopeAssignments.SPObjectID = spo.ID AND ScopeAssignments.LoadingInfoID = @Loc_LoadingInfoID
        JOIN PermissionScopes ON ScopeAssignments.ScopeID = PermissionScopes.ID
        JOIN RoleAssignments ON RoleAssignments.ScopeID = PermissionScopes.ID AND RoleAssignments.LoadingInfoID = @Loc_LoadingInfoID
        Join @OrganisationDescendant as org on org.ContextID=RoleAssignments.PrincipalID
        JOIN PrincipalTypes ON org.PrincipalTypeID=PrincipalTypes.PrincipalTypeID 
        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
        WHERE spo.FarmID = @Loc_FarmID AND EXISTS (SELECT 1 FROM @Loc_SPObjectIDs sp WHERE sp.ID = spo.ID)
			AND (NOT EXISTS (SELECT 1 FROM @Loc_PrincipalTypeIDs) OR EXISTS ( SELECT 1 FROM @Loc_PrincipalTypeIDs pt WHERE pt.ID = org.PrincipalTypeID))
			AND (NOT EXISTS (SELECT 1 FROM @Loc_PermissionTypeIDs) OR EXISTS ( SELECT 1 FROM @Loc_PermissionTypeIDs pt WHERE pt.ID = PermissionTypes.PermissionTypeID))
                                        
        GROUP BY spo.Name, org.ContextId, org.DisplayName,  GivenThroughPrincipals.DisplayName,  Roles.RoleID, Roles.Title, PermissionTypes.Name,spo.Url,
	        org.PrincipalTypeID, PrincipalTypes.Name, org.PrincipalID, org.AccountEnabled
        ORDER BY spo.Name, org.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'Creating [dbo].[GetFeatureActivations]'
GO
CREATE PROCEDURE [dbo].[GetFeatureActivations]
	@FeatureIDs FilterIDsType READONLY,
	@LoadingInfoID int
AS
	SET NOCOUNT ON

	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=@LoadingInfoID                              
    JOIN FeatureScopes ON Features.ScopeID=FeatureScopes.ID
    WHERE FeaturesActivations.Active = 1 AND Features.ScopeID IN (0) AND EXISTS (SELECT 1 FROM @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=@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=@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 @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= @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 @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'Creating [dbo].[GetFeatureActivationsByEdition]'
GO
CREATE PROCEDURE [dbo].[GetFeatureActivationsByEdition]
	@LoadingInfoID int,
	@WebApplicationIDs FilterIDsType READONLY,
	@SiteCollectionIDs FilterIDsType READONLY
AS
	SET NOCOUNT ON

	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=@LoadingInfoID) AND FeaturesActivations.Active = 1
    JOIN v_SharePointObjects spo ON FeaturesActivations.SPObjectID = spo.ID AND FeaturesActivations.LoadingInfoID=@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 @WebApplicationIDs as wf WHERE wf.ID=w.ID ) 
		  AND (NOT EXISTS (SELECT 1 FROM @SiteCollectionIDs) OR EXISTS ( SELECT 1 FROM @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'Creating [dbo].[GetFlattenedRoleAssignments]'
GO
CREATE PROCEDURE [dbo].[GetFlattenedRoleAssignments]
	@SPObjectIDs 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_SPObjectIDs 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_SPObjectIDs
	SELECT * FROM @SPObjectIDs
	
	INSERT INTO @Loc_PrincipalTypeIDs
	SELECT * FROM @PrincipalTypeIDs
	
	INSERT INTO @Loc_PrincipalStatusIDs
	SELECT * FROM @PrincipalStatusIDs
	
	INSERT INTO @Loc_RoleIDs
	SELECT * FROM @RoleIDs
	
	DECLARE @OrganisationDescendant TABLE ( 
	    [LoginName][nvarchar](max),
		[DisplayName][nvarchar](max),
		[PrincipalID] INT, 
		[ContextID] INT,
		[ParentLoginName][nvarchar](max),
		[PrincipalTypeID] INT,
		[PathLevel] INT,
		[AccountEnabled] bit
		)

    DECLARE @Principals TABLE (                                       
                        [PrincipalID] INT                                      
                    )        

    INSERT INTO @Principals([PrincipalID])
		    SELECT c.PrincipalID
		    FROM dbo.Principals AS c 
            JOIN RoleAssignments ON RoleAssignments.PrincipalID=c.PrincipalID AND RoleAssignments.LoadingInfoID = @Loc_LoadingInfoID	
		    JOIN ScopeAssignments ON RoleAssignments.ScopeID = ScopeAssignments.ScopeID AND ScopeAssignments.LoadingInfoID = @Loc_LoadingInfoID	
		    JOIN v_SharePointObjects spo ON ScopeAssignments.SPObjectID = spo.ID 
            WHERE spo.FarmID = @Loc_FarmID AND spo.ID IN (SELECT ID FROM @Loc_SPObjectIDs)       

    DECLARE @Scopes TABLE (                                      
                [ScopeID] INT                                      
                    )      

	INSERT INTO @Scopes([ScopeID])
		    SELECT ScopeAssignments.ScopeID
		    FROM v_SharePointObjects spo                                
		    JOIN ScopeAssignments ON ScopeAssignments.SPObjectID = spo.ID 
            WHERE spo.FarmID = @Loc_FarmID AND spo.ID IN (SELECT ID FROM @Loc_SPObjectIDs) AND ScopeAssignments.LoadingInfoID = @Loc_LoadingInfoID		

    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([LoginName], [DisplayName],[PrincipalID],[ContextID], [ParentLoginName], [PrincipalTypeID],[PathLevel],[AccountEnabled])
				    SELECT principals.Name, principals.DisplayName, principals.PrincipalID, principals.PrincipalID, principals.Name, principals.PrincipalTypeID, 0 AS PathLevel, LoadedPrincipals.AccountEnabled
				    FROM Principals AS principals 
                    JOIN LoadedPrincipals ON LoadedPrincipals.PrincipalID = principals.PrincipalID AND LoadedPrincipals.LoadingInfoID = @Loc_LoadingInfoID
                    WHERE EXISTS (SELECT * FROM @Principals as p WHERE p.PrincipalID=principals.PrincipalID) 
			    END
		    ELSE
			    BEGIN
				    INSERT INTO @OrganisationDescendant([LoginName], [DisplayName],[PrincipalID],[ContextID], [ParentLoginName], [PrincipalTypeID],[PathLevel],[AccountEnabled])
					    SELECT Principals.Name, Principals.DisplayName, Principals.PrincipalID, org.ContextID AS ContextId, principals.Name, Principals.PrincipalTypeID, @Depth, LoadedPrincipals.AccountEnabled
					    FROM PrincipalMemberships 
					    JOIN @OrganisationDescendant AS org on PrincipalMemberships.GroupID = org.PrincipalID and PrincipalMemberships.LoadingInfoID = @Loc_LoadingInfoID and org.PathLevel = (@Depth-1)
					    JOIN Principals ON PrincipalMemberships.UserID = Principals.PrincipalID 			
                        JOIN LoadedPrincipals ON LoadedPrincipals.PrincipalID = Principals.PrincipalID AND LoadedPrincipals.LoadingInfoID = @Loc_LoadingInfoID	
                        WHERE org.ContextID <> Principals.PrincipalID AND org.ParentLoginName <> 'c:0(.s|true' AND org.ParentLoginName <> 'rolemanager|spo-grid-all-users'
		    END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END

        SELECT	
        s.ScopeID as ScopeID,
        Roles.Title AS Title,
        o.DisplayName As PrincipalName,
		o.LoginName as PrincipalUsername,
        PrincipalTypes.Name As Type,
        Roles.RoleID AS [ID],
        o.PrincipalID AS PrincipalID,                                                
        o.PrincipalTypeID as PrincipalTypeID,
        o.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
        FROM @Scopes as s
        JOIN RoleAssignments ON RoleAssignments.ScopeID = s.ScopeID AND RoleAssignments.LoadingInfoID = @Loc_LoadingInfoID	
        Join @OrganisationDescendant as o on o.ContextID=RoleAssignments.PrincipalID
        JOIN PrincipalTypes ON o.PrincipalTypeID=PrincipalTypes.PrincipalTypeID
        JOIN Principals ON o.ContextID = Principals.PrincipalID
        JOIN Roles ON RoleAssignments.RoleID=Roles.RoleID
                                                                            
        WHERE s.ScopeID > 0                                
			  AND (NOT EXISTS (SELECT 1 FROM @Loc_PrincipalTypeIDs) OR EXISTS ( SELECT 1 FROM @Loc_PrincipalTypeIDs pt WHERE pt.ID = o.PrincipalTypeID))
			  AND (NOT EXISTS (SELECT 1 FROM @Loc_PrincipalStatusIDs) OR EXISTS ( SELECT 1 FROM @Loc_PrincipalStatusIDs ps WHERE ps.ID = o.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'Creating [dbo].[GetPathFromSiteCollection]'
GO
CREATE PROCEDURE [dbo].[GetPathFromSiteCollection]
    @ObjectID int,
	@LoadingInfoID int,
	@MaxDepth int
AS
	SET NOCOUNT ON

	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]   
                    )

    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])
				    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]
				    FROM dbo.v_SharePointObjects AS o  
					JOIN dbo.ScopeAssignments as scope ON scope.SPObjectID = o.ID AND scope.LoadingInfoID = @LoadingInfoID
					WHERE  o.ID = @ObjectID                                    				
			    END
		    ELSE
			    BEGIN
				    INSERT INTO @OrganisationDescendant([Name], [ID], [SharePointObjectTypeID], [ParentID], [PathLevel], [HasUniqueRoleAssignments], [IsRootWeb], [PermissionScopeID], [SiteID], [LastUpdated], [WebApplicationID], [Url], [SPGuid])
				        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]
					    FROM SharePointObjectMemberships as m 
					    JOIN @OrganisationDescendant as d on m.ChildID = d.ID and m.LoadingInfoID = @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 = @LoadingInfoID 	
		            END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END
        SELECT	[ID],
			[Name],
            [SharePointObjectTypeID],
            [ParentID],
            [PathLevel],
            [HasUniqueRoleAssignments],
            [IsRootWeb],  
			[PermissionScopeID],
            [SiteID],
            [LastUpdated],
            [WebApplicationID],
            [Url],
            [SPGuid] 
		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'Creating [dbo].[GetRoleAssignments]'
GO
CREATE PROCEDURE [dbo].[GetRoleAssignments]
	@SPObjectIDs 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_SPObjectIDs 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_SPObjectIDs
	SELECT * FROM @SPObjectIDs
	
	INSERT INTO @Loc_PrincipalTypeIDs
	SELECT * FROM @PrincipalTypeIDs
	
	INSERT INTO @Loc_PrincipalStatusIDs
	SELECT * FROM @PrincipalStatusIDs
	
	INSERT INTO @Loc_RoleIDs
	SELECT * FROM @RoleIDs

	SELECT PermissionScopes.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
    FROM v_SharePointObjects spo
        JOIN ScopeAssignments ON ScopeAssignments.SPObjectID = spo.ID AND ScopeAssignments.LoadingInfoID = @Loc_LoadingInfoID
        JOIN PermissionScopes ON ScopeAssignments.ScopeID = PermissionScopes.ID
        JOIN RoleAssignments ON RoleAssignments.ScopeID = PermissionScopes.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 spo.FarmID = @Loc_FarmID                                        
	    AND spo.ID IN (SELECT ID FROM @Loc_SPObjectIDs) 
		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))
		AND (NOT EXISTS (SELECT 1 FROM @Loc_RoleIDs) OR EXISTS ( SELECT 1 FROM @Loc_RoleIDs ps WHERE ps.ID = Roles.RoleID))
    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
PRINT N'Creating [dbo].[GetSharePointStructure]'
GO
CREATE 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
	
	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]   
                                        )

    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])
				    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]
				    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])
				        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]
					    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] 
		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'Creating [dbo].[GetSharePointStructureForWebApplications]'
GO
CREATE 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

	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]        
                    )

    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])
				    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]
                    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 @OrganisationDescendant([Name], [ID], [SharePointObjectTypeID], [ParentID], [PathLevel], [HasUniqueRoleAssignments], [IsRootWeb], [PermissionScopeID], [SiteID], [LastUpdated], [WebApplicationID], [Url], [SPGuid])
				        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]
					    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] 
		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].[GetSiteCollectionsForWebApplication]'
GO
ALTER PROCEDURE [dbo].[GetSiteCollectionsForWebApplication]
	@LoadingInfoID INT,
	@FarmID INT,
	@WebApplicationID INT
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @SharePointObjectNodes TABLE
	( 
		[ID] [int],   
		[Url] [nvarchar](255),
		[Name] [nvarchar](255),
		[SPGuid] [uniqueidentifier])

	INSERT INTO @SharePointObjectNodes([ID], [Url], [Name], [SPGuid])
		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] = @LoadingInfoID AND [dbo].[LoadedSiteCollections].[WebApplicationID] = @WebApplicationID
		SELECT * FROM @SharePointObjectNodes 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
	
	DECLARE @SharePointObjectNodes TABLE
	( 
		[ID] [int],   
		[Url] [nvarchar](255),
		[Name] [nvarchar](255),
		[SPGuid] [uniqueidentifier],
		[HasChildren] [bit])

	INSERT INTO @SharePointObjectNodes([ID], [Url], [Name], [SPGuid], [HasChildren])
		SELECT	[WebApplication].[ID],
				[WebApplication].[Url], 
				[WebApplication].[Name],	
				[WebApplication].[SPGuid],			
				(CASE WHEN EXISTS (SELECT [LoadedSiteCollection].[SiteCollectionID]
				FROM [dbo].[LoadedSiteCollections] AS [LoadedSiteCollection] WHERE [LoadedSiteCollection].[LoadingInfoID] = @LoadingInfoID AND [LoadedSiteCollection].[WebApplicationID] = [WebApplication].[ID])						
				THEN 1 ELSE 0 END)
		FROM [dbo].[v_WebApplications] AS [WebApplication]
		JOIN [dbo].[LoadedWebApplications] AS [LoadedWebApplication] ON [LoadedWebApplication].[WebApplicationID] = [WebApplication].[ID] 
		WHERE [WebApplication].[FarmID] = @FarmID AND [LoadedWebApplication].[LoadingInfoID] = @LoadingInfoID

	SELECT * FROM @SharePointObjectNodes 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
	
	DECLARE @SharePointObjectNodes TABLE
	( 
		[ID] [int],   
		[Url] [nvarchar](255),
		[Name] [nvarchar](255),
		[SPGuid] [uniqueidentifier])

	INSERT INTO @SharePointObjectNodes([ID], [Url], [Name], [SPGuid])
		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] = @FarmID AND [LoadedWebApplication].[LoadingInfoID] = @LoadingInfoID

	SELECT * FROM @SharePointObjectNodes 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

	DECLARE @SharePointObjectNodes TABLE
	( 
		[ID] [int],   
		[Url] [nvarchar](255),
		[Name] [nvarchar](255),
		[SPGuid] [uniqueidentifier])

		INSERT INTO @SharePointObjectNodes([ID], [Url], [Name], [SPGuid])
			SELECT	[SPObject].[ID],
					[SPObject].[Url], 
					[SPObject].[Name],
					[SPObject].[SPGuid]
			FROM [dbo].[v_SharePointObjects] AS [SPObject] 
			JOIN [dbo].[SharePointObjectMemberships] AS [Membership] ON [Membership].[ParentID] = @ParentID AND [Membership].[LoadingInfoID] = @LoadingInfoID AND [Membership].[ChildID] = [SPObject].[ID] AND [SPObject].[SPObjectTypeID] = 2
		SELECT * FROM @SharePointObjectNodes 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'Creating [dbo].[GetUnusedContent]'
GO
CREATE 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

	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]   
                                        )

    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])
				    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]
				    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])
				        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]
					    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 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] 
		FROM  @OrganisationDescendant 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'Creating [dbo].[GetUsersPerSite]'
GO
CREATE PROCEDURE [dbo].[GetUsersPerSite]
	@LoadingInfoID int
AS
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 = @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 = @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 = @LoadingInfoID AND pps.SiteId = lsc.SiteCollectionID
		JOIN v_SharePointObjects spo ON spo.ID = lsc.SiteCollectionID
		JOIN LoadedPrincipals AS lp ON lp.LoadingInfoID = @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'Creating [dbo].[GetWebApplicationPermissions]'
GO
CREATE PROCEDURE [dbo].[GetWebApplicationPermissions]
	@WebApplicationID int,
	@LoadingInfoID int,
	@PrincipalTypeIDs FilterIDsType READONLY,
	@PrincipalStatusIDs FilterIDsType READONLY
AS
	SET NOCOUNT ON

	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
        FROM v_WebApplications w
        JOIN WebApplicationPermissions ON WebApplicationPermissions.WebApplicationID=w.ID AND WebApplicationPermissions.LoadingInfoID = @LoadingInfoID
        JOIN Principals ON WebApplicationPermissions.PrincipalID=Principals.PrincipalID AND WebApplicationPermissions.LoadingInfoID = @LoadingInfoID
        JOIN LoadedPrincipals ON Principals.PrincipalID=LoadedPrincipals.PrincipalID AND LoadedPrincipals.LoadingInfoID = @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 = @LoadingInfoID
        WHERE w.ID = @WebApplicationID
			  AND (NOT EXISTS (SELECT 1 FROM @PrincipalTypeIDs) OR EXISTS ( SELECT 1 FROM @PrincipalTypeIDs pt WHERE pt.ID = PrincipalTypes.PrincipalTypeID))
			  AND (NOT EXISTS (SELECT 1 FROM @PrincipalStatusIDs) OR EXISTS ( SELECT 1 FROM @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
PRINT N'Creating [dbo].[ReorganizeIndexes]'
GO
CREATE PROCEDURE [dbo].[ReorganizeIndexes]
AS
BEGIN
	SET NOCOUNT ON
		
			DECLARE @Indexes TABLE (
		RowID int IDENTITY(1, 1), 
		[Table] NVARCHAR(255),
		[Index] NVARCHAR(255),
		AvgFragmentation FLOAT,
		Operation NVARCHAR(50) NULL
	)

	DECLARE @NumberRecords INT
	DECLARE @RowCount INT
	DECLARE @Tname NVARCHAR(250)
	DECLARE @Iname NVARCHAR(250)
	DECLARE @Command NVARCHAR(1000)
	DECLARE @Operation NVARCHAR(MAX)

	INSERT INTO @Indexes([Table], [Index], AvgFragmentation, Operation)
	SELECT
		(DBS.name + '.' + DBT.name) AS 'Table'
		,DBI.name AS 'Index'
		,IDXS.avg_fragmentation_in_percent AS AvgFragmentation
		,Operation = 
		CASE	WHEN IDXS.avg_fragmentation_in_percent > 30  THEN N'REBUILD' 
				WHEN IDXS.avg_fragmentation_in_percent > 5 THEN N'REORGANIZE'
		END 
	FROM 
		sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS IDXS
		INNER JOIN sys.tables AS DBT ON DBT.[object_id] = IDXS.[object_id] 
		INNER JOIN sys.schemas AS DBS ON DBT.[schema_id] = DBS.[schema_id] 
		INNER JOIN sys.indexes AS DBI ON DBI.[object_id] = IDXS.[object_id] AND IDXS.index_id = DBI.index_id
		WHERE IDXS.page_count > 500 AND IDXS.avg_fragmentation_in_percent > 5

	SET @NumberRecords = @@ROWCOUNT
	SET @RowCount = 1
	WHILE @RowCount <= @NumberRecords
	BEGIN
		BEGIN TRY
				SELECT @Tname = [@Indexes].[Table], @Iname =  [@Indexes].[Index], @Operation = [@Indexes].[Operation]
				FROM @Indexes
				WHERE RowID = @RowCount						

				EXECUTE (@Command)

				SET @Command= N'ALTER INDEX ' + @Iname + ' ON '+ @Tname + ' ' + @Operation			 
				EXECUTE (@Command)
				IF(@Operation = N'REORGANIZE')
				BEGIN
					SET @Command= N'UPDATE STATISTICS ' + @Tname + ' '+ @Iname
					EXECUTE (@Command)
				END
		END TRY
		BEGIN CATCH
		END CATCH
		SET @RowCount = @RowCount + 1
	END
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'Creating primary key [PK_ScopeAssignments] on [dbo].[ScopeAssignments]'
GO
ALTER TABLE [dbo].[ScopeAssignments] ADD CONSTRAINT [PK_ScopeAssignments] PRIMARY KEY CLUSTERED  ([LoadingInfoID], [SPObjectID])
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 [ScopeAssignments_LoadingInfoID_ScopeID] on [dbo].[ScopeAssignments]'
GO
CREATE NONCLUSTERED INDEX [ScopeAssignments_LoadingInfoID_ScopeID] ON [dbo].[ScopeAssignments] ([LoadingInfoID], [ScopeID]) INCLUDE ([SPObjectID])
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'Adding foreign keys to [dbo].[AnonymousAccessLinks]'
GO
ALTER TABLE [dbo].[AnonymousAccessLinks] ADD CONSTRAINT [FK_AnonymousAccessLinks_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID])
ALTER TABLE [dbo].[AnonymousAccessLinks] ADD CONSTRAINT [FK_AnonymousAccessLinks_SharePointObjects] FOREIGN KEY ([SharePointObjectID]) REFERENCES [dbo].[SharePointObjects] ([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'Adding foreign keys to [dbo].[PrincipalMemberships]'
GO
ALTER TABLE [dbo].[PrincipalMemberships] ADD CONSTRAINT [FK_PrincipalMemberships_Principals] FOREIGN KEY ([GroupID]) REFERENCES [dbo].[Principals] ([PrincipalID])
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'Adding foreign keys to [dbo].[RoleAssignments]'
GO
ALTER TABLE [dbo].[RoleAssignments] ADD CONSTRAINT [FK_RoleAssignments_Principals] FOREIGN KEY ([PrincipalID]) REFERENCES [dbo].[Principals] ([PrincipalID])
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'Adding foreign keys to [dbo].[SiteCollectionProperties]'
GO
ALTER TABLE [dbo].[SiteCollectionProperties] ADD CONSTRAINT [FK_SiteCollectionProperties_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID])
ALTER TABLE [dbo].[SiteCollectionProperties] ADD CONSTRAINT [FK_SiteCollectionProperties_SharePointObjects] FOREIGN KEY ([SiteCollectionID]) REFERENCES [dbo].[SharePointObjects] ([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'Adding foreign keys to [dbo].[SPDocKitRoleFarms]'
GO
ALTER TABLE [dbo].[SPDocKitRoleFarms] ADD CONSTRAINT [FK_SPDocKitRoleFarms_Farms_SPDocKitRoles] FOREIGN KEY ([RoleID]) REFERENCES [dbo].[SPDocKitRoles] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[SPDocKitRoleFarms] ADD CONSTRAINT [FK_SPDocKitRoleFarms_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([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'Adding foreign keys to [dbo].[SPDocKitRoleSiteCollections]'
GO
ALTER TABLE [dbo].[SPDocKitRoleSiteCollections] ADD CONSTRAINT [FK_SPDocKitRoleSiteCollections_SPDocKitRoles] FOREIGN KEY ([RoleID]) REFERENCES [dbo].[SPDocKitRoles] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[SPDocKitRoleSiteCollections] ADD CONSTRAINT [FK_SPDocKitRoleSiteCollections_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([ID])
ALTER TABLE [dbo].[SPDocKitRoleSiteCollections] ADD CONSTRAINT [FK_SPDocKitRoleSiteCollections_WebApplications] FOREIGN KEY ([WebApplicationID]) REFERENCES [dbo].[WebApplications] ([ID])
ALTER TABLE [dbo].[SPDocKitRoleSiteCollections] ADD CONSTRAINT [FK_SPDocKitRoleSiteCollections_SharePointObjects] FOREIGN KEY ([SiteCollectionID]) REFERENCES [dbo].[SharePointObjects] ([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'Adding foreign keys to [dbo].[SPDocKitUserRoles]'
GO
ALTER TABLE [dbo].[SPDocKitUserRoles] ADD CONSTRAINT [FK_SPDocKitUserRoles_SPDocKitRoles] FOREIGN KEY ([RoleID]) REFERENCES [dbo].[SPDocKitRoles] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[SPDocKitUserRoles] ADD CONSTRAINT [FK_SPDocKitUserRoles_SPDocKitUsers] FOREIGN KEY ([UserID]) REFERENCES [dbo].[SPDocKitUsers] ([ID]) ON DELETE CASCADE
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'Adding foreign keys to [dbo].[SPDocKitSiteCollectionAdministrators]'
GO
ALTER TABLE [dbo].[SPDocKitSiteCollectionAdministrators] ADD CONSTRAINT [FK_SPDocKitSiteCollectionAdministrators_Principals] FOREIGN KEY ([PrincipalID]) REFERENCES [dbo].[Principals] ([PrincipalID])
ALTER TABLE [dbo].[SPDocKitSiteCollectionAdministrators] ADD CONSTRAINT [FK_SPDocKitSiteCollectionAdministrators_SharePointObjects] FOREIGN KEY ([SiteID]) REFERENCES [dbo].[SharePointObjects] ([ID])
ALTER TABLE [dbo].[SPDocKitSiteCollectionAdministrators] ADD CONSTRAINT [FK_SPDocKitSiteCollectionAdministrators_WebApplications] FOREIGN KEY ([WebApplicationID]) REFERENCES [dbo].[WebApplications] ([ID])
ALTER TABLE [dbo].[SPDocKitSiteCollectionAdministrators] ADD CONSTRAINT [FK_SPDocKitSiteCollectionAdministrators_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([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'Adding foreign keys to [dbo].[SPDocKitUsers]'
GO
ALTER TABLE [dbo].[SPDocKitUsers] ADD CONSTRAINT [FK_SPDocKitUsers_PrincipalTypes] FOREIGN KEY ([PrincipalTypeID]) REFERENCES [dbo].[PrincipalTypes] ([PrincipalTypeID])
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'Adding foreign keys to [dbo].[ScopeAssignments]'
GO
ALTER TABLE [dbo].[ScopeAssignments] ADD CONSTRAINT [FK_ScopeAssignments_LoadingInfo] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID])
ALTER TABLE [dbo].[ScopeAssignments] ADD CONSTRAINT [FK_ScopeAssignments_SharePointObjects] FOREIGN KEY ([SPObjectID]) REFERENCES [dbo].[SharePointObjects] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[ScopeAssignments] ADD CONSTRAINT [FK_ScopeAssignments_PermissionScope] FOREIGN KEY ([ScopeID]) REFERENCES [dbo].[PermissionScopes] ([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 permissions on [dbo].[FilterIDsType]'
GO
GRANT EXECUTE ON TYPE:: [dbo].[FilterIDsType] TO [spdockit_monitoring_viewer]
GRANT EXECUTE ON TYPE:: [dbo].[FilterIDsType] TO [spdockit_report_viewer]
GRANT EXECUTE ON TYPE:: [dbo].[FilterIDsType] TO [spdockit_snapshot_viewer]
GO
PRINT N'Altering permissions on [dbo].[ContentType]'
GO
GRANT SELECT ON  [dbo].[ContentType] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[FeatureEditions]'
GO
GRANT SELECT ON  [dbo].[FeatureEditions] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[FeatureLicenses]'
GO
GRANT SELECT ON  [dbo].[FeatureLicenses] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[Features]'
GO
GRANT SELECT ON  [dbo].[Features] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[FeaturesActivations]'
GO
GRANT SELECT ON  [dbo].[FeaturesActivations] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[FeatureScopes]'
GO
GRANT SELECT ON  [dbo].[FeatureScopes] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[FeaturesLoaded]'
GO
GRANT SELECT ON  [dbo].[FeaturesLoaded] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[LoadedPrincipals]'
GO
GRANT SELECT ON  [dbo].[LoadedPrincipals] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[LoadedSiteCollections]'
GO
GRANT SELECT ON  [dbo].[LoadedSiteCollections] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[LoadedWebApplications]'
GO
GRANT SELECT ON  [dbo].[LoadedWebApplications] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[LoadingInfos]'
GO
GRANT SELECT ON  [dbo].[LoadingInfos] TO [spdockit_monitoring_viewer]
GRANT SELECT ON  [dbo].[LoadingInfos] TO [spdockit_report_viewer]
GRANT SELECT ON  [dbo].[LoadingInfos] TO [spdockit_snapshot_viewer]
GO
PRINT N'Altering permissions on [dbo].[MonitoringEventLogs]'
GO
GRANT SELECT ON  [dbo].[MonitoringEventLogs] TO [spdockit_monitoring_viewer]
GO
PRINT N'Altering permissions on [dbo].[MonitoringServers]'
GO
GRANT SELECT ON  [dbo].[MonitoringServers] TO [spdockit_monitoring_viewer]
GRANT SELECT ON  [dbo].[MonitoringServers] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[MonitoringUlsEvents]'
GO
GRANT SELECT ON  [dbo].[MonitoringUlsEvents] TO [spdockit_monitoring_viewer]
GO
PRINT N'Altering permissions on [dbo].[Options]'
GO
GRANT SELECT ON  [dbo].[Options] TO [spdockit_monitoring_viewer]
GRANT SELECT ON  [dbo].[Options] TO [spdockit_report_viewer]
GRANT SELECT ON  [dbo].[Options] TO [spdockit_snapshot_viewer]
GO
PRINT N'Altering permissions on [dbo].[PermissionGroupTypes]'
GO
GRANT SELECT ON  [dbo].[PermissionGroupTypes] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[PermissionScopes]'
GO
GRANT SELECT ON  [dbo].[PermissionScopes] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[PermissionTypes]'
GO
GRANT SELECT ON  [dbo].[PermissionTypes] TO [spdockit_monitoring_viewer]
GRANT SELECT ON  [dbo].[PermissionTypes] TO [spdockit_report_viewer]
GRANT SELECT ON  [dbo].[PermissionTypes] TO [spdockit_snapshot_viewer]
GO
PRINT N'Altering permissions on [dbo].[PolicyLevels]'
GO
GRANT SELECT ON  [dbo].[PolicyLevels] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[PolicyPermissions]'
GO
GRANT SELECT ON  [dbo].[PolicyPermissions] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[PrincipalMemberships]'
GO
GRANT SELECT ON  [dbo].[PrincipalMemberships] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[Principals]'
GO
GRANT SELECT ON  [dbo].[Principals] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[PrincipalTypes]'
GO
GRANT SELECT ON  [dbo].[PrincipalTypes] TO [spdockit_monitoring_viewer]
GRANT SELECT ON  [dbo].[PrincipalTypes] TO [spdockit_report_viewer]
GRANT SELECT ON  [dbo].[PrincipalTypes] TO [spdockit_snapshot_viewer]
GO
PRINT N'Altering permissions on [dbo].[ReportViews]'
GO
GRANT SELECT ON  [dbo].[ReportViews] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[RoleAssignments]'
GO
GRANT SELECT ON  [dbo].[RoleAssignments] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[RolePermissions]'
GO
GRANT SELECT ON  [dbo].[RolePermissions] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[Roles]'
GO
GRANT SELECT ON  [dbo].[Roles] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[RuleHistory]'
GO
GRANT SELECT ON  [dbo].[RuleHistory] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[Rules]'
GO
GRANT SELECT ON  [dbo].[Rules] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[RuleTypes]'
GO
GRANT SELECT ON  [dbo].[RuleTypes] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[SandboxedSolutions]'
GO
GRANT SELECT ON  [dbo].[SandboxedSolutions] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[ScopeAssignments]'
GO
GRANT SELECT ON  [dbo].[ScopeAssignments] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[SharePointObjectContentType]'
GO
GRANT SELECT ON  [dbo].[SharePointObjectContentType] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[SharePointObjectMemberships]'
GO
GRANT SELECT ON  [dbo].[SharePointObjectMemberships] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[SharePointObjectTypes]'
GO
GRANT SELECT ON  [dbo].[SharePointObjectTypes] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[SiteCollectionAdministrators]'
GO
GRANT SELECT ON  [dbo].[SiteCollectionAdministrators] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[SiteCollectionProperties]'
GO
GRANT SELECT ON  [dbo].[SiteCollectionProperties] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[SiteCollectionsContentDatabase]'
GO
GRANT SELECT ON  [dbo].[SiteCollectionsContentDatabase] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[WebApplicationPermissions]'
GO
GRANT SELECT ON  [dbo].[WebApplicationPermissions] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[WebApplicationSettings]'
GO
GRANT SELECT ON  [dbo].[WebApplicationSettings] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[WebApplicationZones]'
GO
GRANT SELECT ON  [dbo].[WebApplicationZones] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[WorkflowAssociations]'
GO
GRANT SELECT ON  [dbo].[WorkflowAssociations] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[WorkflowDefinitionRestrictionTypes]'
GO
GRANT SELECT ON  [dbo].[WorkflowDefinitionRestrictionTypes] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[WorkflowDefinitions]'
GO
GRANT SELECT ON  [dbo].[WorkflowDefinitions] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[WorkflowSubscriptions]'
GO
GRANT SELECT ON  [dbo].[WorkflowSubscriptions] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[WorkflowTemplates]'
GO
GRANT SELECT ON  [dbo].[WorkflowTemplates] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[WorkflowTemplateSiteCollectionProperties]'
GO
GRANT SELECT ON  [dbo].[WorkflowTemplateSiteCollectionProperties] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[WorkflowTemplatesOnSubSite]'
GO
GRANT SELECT ON  [dbo].[WorkflowTemplatesOnSubSite] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[WorkflowTemplatesTemplateTypes]'
GO
GRANT SELECT ON  [dbo].[WorkflowTemplatesTemplateTypes] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[WorkflowTemplateTypes]'
GO
GRANT SELECT ON  [dbo].[WorkflowTemplateTypes] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[v_FarmDescriptions]'
GO
GRANT SELECT ON  [dbo].[v_FarmDescriptions] TO [spdockit_snapshot_viewer]
GO
PRINT N'Altering permissions on [dbo].[v_Farms]'
GO
GRANT SELECT ON  [dbo].[v_Farms] TO [spdockit_monitoring_viewer]
GRANT SELECT ON  [dbo].[v_Farms] TO [spdockit_report_viewer]
GRANT SELECT ON  [dbo].[v_Farms] TO [spdockit_snapshot_viewer]
GO
PRINT N'Altering permissions on [dbo].[v_HasAccessToAllSPObjects]'
GO
GRANT SELECT ON  [dbo].[v_HasAccessToAllSPObjects] TO [spdockit_monitoring_viewer]
GRANT SELECT ON  [dbo].[v_HasAccessToAllSPObjects] TO [spdockit_report_viewer]
GRANT SELECT ON  [dbo].[v_HasAccessToAllSPObjects] TO [spdockit_snapshot_viewer]
GO
PRINT N'Altering permissions on [dbo].[v_IsUserAdmin]'
GO
GRANT SELECT ON  [dbo].[v_IsUserAdmin] TO [spdockit_monitoring_viewer]
GRANT SELECT ON  [dbo].[v_IsUserAdmin] TO [spdockit_report_viewer]
GRANT SELECT ON  [dbo].[v_IsUserAdmin] TO [spdockit_snapshot_viewer]
GO
PRINT N'Altering permissions on [dbo].[v_MySPDocKitRoles]'
GO
GRANT SELECT ON  [dbo].[v_MySPDocKitRoles] TO [spdockit_monitoring_viewer]
GRANT SELECT ON  [dbo].[v_MySPDocKitRoles] TO [spdockit_report_viewer]
GRANT SELECT ON  [dbo].[v_MySPDocKitRoles] TO [spdockit_snapshot_viewer]
GO
PRINT N'Altering permissions on [dbo].[v_Principals]'
GO
GRANT SELECT ON  [dbo].[v_Principals] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[v_SharePointObjects]'
GO
GRANT SELECT ON  [dbo].[v_SharePointObjects] TO [spdockit_monitoring_viewer]
GRANT SELECT ON  [dbo].[v_SharePointObjects] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[v_SPObjectsWithAccess]'
GO
GRANT SELECT ON  [dbo].[v_SPObjectsWithAccess] TO [spdockit_monitoring_viewer]
GRANT SELECT ON  [dbo].[v_SPObjectsWithAccess] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[v_WebApplicationConfigs]'
GO
GRANT SELECT ON  [dbo].[v_WebApplicationConfigs] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[v_WebApplications]'
GO
GRANT SELECT ON  [dbo].[v_WebApplications] TO [spdockit_monitoring_viewer]
GRANT SELECT ON  [dbo].[v_WebApplications] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetChildrenForParent]'
GO
GRANT EXECUTE ON  [dbo].[GetChildrenForParent] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetContentTypeListUsage]'
GO
GRANT EXECUTE ON  [dbo].[GetContentTypeListUsage] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetDetailedPermissions]'
GO
GRANT EXECUTE ON  [dbo].[GetDetailedPermissions] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetFeatureActivations]'
GO
GRANT EXECUTE ON  [dbo].[GetFeatureActivations] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetFeatureActivationsByEdition]'
GO
GRANT EXECUTE ON  [dbo].[GetFeatureActivationsByEdition] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetFlattenedGroupMembers]'
GO
GRANT EXECUTE ON  [dbo].[GetFlattenedGroupMembers] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetFlattenedRoleAssignments]'
GO
GRANT EXECUTE ON  [dbo].[GetFlattenedRoleAssignments] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetFlattenedRoleAssignmentsForScopeIds]'
GO
GRANT EXECUTE ON  [dbo].[GetFlattenedRoleAssignmentsForScopeIds] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetPathFromSiteCollection]'
GO
GRANT EXECUTE ON  [dbo].[GetPathFromSiteCollection] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetRoleAssignments]'
GO
GRANT EXECUTE ON  [dbo].[GetRoleAssignments] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetRoleAssignmentsForScopeIDs]'
GO
GRANT EXECUTE ON  [dbo].[GetRoleAssignmentsForScopeIDs] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetSharePointStructure]'
GO
GRANT EXECUTE ON  [dbo].[GetSharePointStructure] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetSharePointStructureForWebApplications]'
GO
GRANT EXECUTE ON  [dbo].[GetSharePointStructureForWebApplications] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetSiteCollectionsForWebApplication]'
GO
GRANT EXECUTE ON  [dbo].[GetSiteCollectionsForWebApplication] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetSPWebApplicationsForLoadingInfo]'
GO
GRANT EXECUTE ON  [dbo].[GetSPWebApplicationsForLoadingInfo] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetSPWebApplicationsForLoadingInfoWithoutChildren]'
GO
GRANT EXECUTE ON  [dbo].[GetSPWebApplicationsForLoadingInfoWithoutChildren] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetSubsitesOfSite]'
GO
GRANT EXECUTE ON  [dbo].[GetSubsitesOfSite] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetUnusedContent]'
GO
GRANT EXECUTE ON  [dbo].[GetUnusedContent] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetUserPermissionAssignments]'
GO
GRANT EXECUTE ON  [dbo].[GetUserPermissionAssignments] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetUsersPerSite]'
GO
GRANT EXECUTE ON  [dbo].[GetUsersPerSite] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetWebApplicationPermissions]'
GO
GRANT EXECUTE ON  [dbo].[GetWebApplicationPermissions] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[IsAllowedToRunSPDocKit]'
GO
GRANT EXECUTE ON  [dbo].[IsAllowedToRunSPDocKit] TO [public]
GO
INSERT INTO DateRanges(DateRangeID, Name) VALUES(6, 'Immediately after service snapshot')
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO Options (OptionID, OptionValue) VALUES (2000, 'false')
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO [dbo].[SPDocKitRoles]
           ([Name]
           ,[Description]
           ,[RolePermissions]
		   ,[SPObjectSharingPolicy])
     VALUES
           ('Admins'
           ,'Has access to all reports, can create new roles and manage role memberships.'
           ,'<ArrayOfUIElementSecurityEnum xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
			  <UIElementSecurityEnum>Snapshots</UIElementSecurityEnum>
			  <UIElementSecurityEnum>SiteExplorer</UIElementSecurityEnum>
			  <UIElementSecurityEnum>PermissionExplorer</UIElementSecurityEnum>
			  <UIElementSecurityEnum>PermissionReports</UIElementSecurityEnum>
			  <UIElementSecurityEnum>ContentReports</UIElementSecurityEnum>
			  <UIElementSecurityEnum>Monitoring</UIElementSecurityEnum>
			</ArrayOfUIElementSecurityEnum>'
		   ,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
INSERT INTO [dbo].[SPDocKitRoles]
           ([Name]
           ,[Description]
           ,[RolePermissions]
		   ,[SPObjectSharingPolicy])
     VALUES
           ('Viewers'
           ,'Has access to all reports.'
           ,'<ArrayOfUIElementSecurityEnum xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
			  <UIElementSecurityEnum>Snapshots</UIElementSecurityEnum>
			  <UIElementSecurityEnum>SiteExplorer</UIElementSecurityEnum>
			  <UIElementSecurityEnum>PermissionExplorer</UIElementSecurityEnum>
			  <UIElementSecurityEnum>PermissionReports</UIElementSecurityEnum>
			  <UIElementSecurityEnum>ContentReports</UIElementSecurityEnum>
			  <UIElementSecurityEnum>Monitoring</UIElementSecurityEnum>
			</ArrayOfUIElementSecurityEnum>'
		   ,1)

DECLARE @ViewerRoleID int
SELECT @ViewerRoleID = ID 
FROM [dbo].[SPDocKitRoles]
WHERE Name = 'Viewers'

INSERT INTO [dbo].[SPDocKitRoleFarms] (RoleID, FarmID)
SELECT @ViewerRoleID, f.ID
FROM [dbo].[Farms] f
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
GRANT EXECUTE ON OBJECT::[dbo].[IsAllowedToRunSPDocKit] TO public
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DECLARE @OrganisationDescendant TABLE ( 
		[PrincipalID] INT, 
		[ContextID] INT, 
		[PathLevel] INT,
		[SiteCollectionID] INT,
		[WebApplicationID] INT,
		[FarmID] INT,
		[LoadingInfoID] 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 @OrganisationDescendant([PrincipalID], [ContextID], [PathLevel], [SiteCollectionID], [WebApplicationID], [FarmID], [LoadingInfoID])
			SELECT pm.UserID, pm.UserID, 0 AS PathLevel, lsc.SiteCollectionID, lsc.WebApplicationID, l.FarmID, l.ID
			FROM [dbo].[LoadingInfos] l
			INNER JOIN [dbo].[LoadedSiteCollections] lsc ON lsc.LoadingInfoID = l.ID
			INNER JOIN [dbo].[Principals] p ON p.SiteID = lsc.SiteCollectionID AND p.Name = 'SiteAdministrators' AND p.FarmID = l.FarmID AND p.SiteID = lsc.SiteCollectionID
			INNER JOIN [dbo].[PrincipalMemberships] pm ON pm.GroupID = p.PrincipalID AND pm.LoadingInfoID = l.ID	
			WHERE l.ID IN (SELECT MAX(loadinInfoPerFarm.[ID]) FROM [dbo].[LoadingInfos] loadinInfoPerFarm
													WHERE loadinInfoPerFarm.[PermissionsLoaded] = 1
													GROUP BY loadinInfoPerFarm.[FarmID])		
		END
	ELSE
		BEGIN
			INSERT INTO @OrganisationDescendant([PrincipalID], [ContextID], [PathLevel], [SiteCollectionID], [WebApplicationID], [FarmID], [LoadingInfoID])
				SELECT Principals.PrincipalID, org.ContextID, @Depth, org.SiteCollectionID, org.WebApplicationID, org.FarmID, org.LoadingInfoID
				FROM PrincipalMemberships 
				JOIN @OrganisationDescendant AS org on PrincipalMemberships.GroupID = org.PrincipalID and PrincipalMemberships.LoadingInfoID = org.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

--zapisi tko su novi site collection admini
INSERT INTO [dbo].[SPDocKitSiteCollectionAdministrators]
        ([PrincipalID]
        ,[SiteID]
        ,[WebApplicationID]
        ,[FarmID])
SELECT DISTINCT org.PrincipalID,
		org.SiteCollectionID,
		org.WebApplicationID,
		org.FarmID	
FROM @OrganisationDescendant org
INNER JOIN [dbo].[Principals] p ON org.PrincipalID = p.PrincipalID
WHERE p.PrincipalTypeID = 2 --zanimaju nas samo useri ne i AD grupe
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.1.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

