POA-transfer.sql

-- Based on http://support2.microsoft.com/kb/2664150
-- Change DB
use [test_MSCRM]


BEGIN TRY
IF EXISTS (SELECT * FROM sys.sysobjects
WHERE id = object_id(N'[dbo].[PrincipalObjectAccess_New]')
AND ObjectProperty(id, N'IsUserTable') = 1)
 RAISERROR ('ERROR: PrincipalObjectAccess_New already exists!!!', 16,-1); 

IF EXISTS (SELECT * FROM sys.sysobjects
WHERE id = object_id(N'[dbo].[PrincipalObjectAccess_Old]')
AND ObjectProperty(id, N'IsUserTable') = 1)
 RAISERROR ('ERROR: PrincipalObjectAccess_Old already exists!!!', 16,-1); 

begin tran T1

IF NOT EXISTS (SELECT * FROM sys.sysobjects
WHERE id = object_id(N'[dbo].[ToDeletePOAEntries]')
AND ObjectProperty(id, N'IsUserTable') = 1)
create table ToDeletePoaEntries
(
PrincipalObjectAccessId uniqueidentifier PRIMARY KEY not null,
)

----- Insert records to be deleted in ToDeletePoaEntries

-- go through all user-owned entities which are not replicated and don't support duplicate detection
declare entity_cursor cursor local FORWARD_ONLY READ_ONLY
for select distinct e.ObjectTypeCode, e.BaseTableName, a.PhysicalName from EntityView e
inner join AttributeView a on e.EntityId = a.EntityId and a.IsPKAttribute = 1
where e.IsReplicated = 0 and e.IsDuplicateCheckSupported = 0 and e.OwnershipTypeMask & 1 = 1

open entity_cursor

declare @baseTableName sysname
declare @otc nvarchar(20)
declare @primaryKey sysname
declare @totalCollected int = 0
declare @currentCollected int

declare @tempRowCount int = 0
declare @collectstatement nvarchar(max)

fetch next from entity_cursor
into @otc, @baseTableName, @primaryKey

while @@FETCH_STATUS = 0
begin
print 'Cleaning up POA for ' + @baseTableName

set @currentCollected = 0



set @collectstatement = 'insert into ToDeletePoaEntries(PrincipalObjectAccessId) 
select poa.PrincipalObjectAccessId
from PrincipalObjectAccess poa
left join ' + @baseTableName + ' e on poa.ObjectId = e.' + @primaryKey + 
' where e.' + @primaryKey + ' is null and poa.ObjectTypeCode = ' + @otc;

print @collectstatement

exec(@collectstatement)
set @tempRowCount = @@ROWCOUNT
set @currentCollected = @currentCollected + @tempRowCount
print CAST(@currentCollected as nvarchar(20)) + ' records collected for ' + @baseTableName
set @totalCollected = @totalCollected + @currentCollected

fetch next from entity_cursor
into @otc, @baseTableName, @primaryKey
end

close entity_cursor
deallocate entity_cursor

print CAST(@totalCollected as nvarchar(20)) + ' total records collected'

-- INSERT INTO temporary table

select poa.*
into PrincipalObjectAccess_New
from PrincipalObjectAccess poa
left join [ToDeletePoaEntries] td on poa.PrincipalObjectAccessId = td.PrincipalObjectAccessId
where td.PrincipalObjectAccessId IS null;

--RENAMING old indexes, constraints and table 
exec sp_rename N'DF_PrincipalObjectAccess_PrincipalObjectAccessId', N'DF_PrincipalObjectAccess_PrincipalObjectAccessId_old', 'Object'
exec sp_rename N'DF_PrincipalObjectAccess_InheritedAccessRightsMask', N'DF_PrincipalObjectAccess_InheritedAccessRightsMask_OLD', 'Object'
exec sp_rename N'PrincipalObjectAccess.ndx_Cover',N'ndx_Cover_old',N'INDEX'
exec sp_rename N'PrincipalObjectAccess.fndx_Sync_VersionNumber',N'ndxfndx_Sync_VersionNumber_old','INDEX'
exec sp_rename N'PrincipalObjectAccess.cndx_PrincipalObjectAccess',N'cndx_PrincipalObjectAccess_old','INDEX'
exec sp_rename N'UQ_PrincipalObjectAccess', N'UQ_PrincipalObjectAccess_OLD', 'Object'
exec sp_rename N'ndx_PrimaryKey_PrincipalObjectAccess', N'ndx_PrimaryKey_PrincipalObjectAccess_OLD', 'Object'
exec sp_rename N'PrincipalObjectAccess', N'PrincipalObjectAccess_old', 'Object'

--renaming new table
exec sp_rename N'PrincipalObjectAccess_New', N'PrincipalObjectAccess'

-- Create original indexes and constraints

 ALTER TABLE [dbo].[PrincipalObjectAccess] ADD  CONSTRAINT [ndx_PrimaryKey_PrincipalObjectAccess] PRIMARY KEY NONCLUSTERED 
(
	[PrincipalObjectAccessId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
ALTER TABLE [dbo].[PrincipalObjectAccess] ADD  CONSTRAINT [UQ_PrincipalObjectAccess] UNIQUE NONCLUSTERED 
(
	[PrincipalId] ASC,
	[ObjectId] ASC,
	[ObjectTypeCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

/****** Object:  Index [cndx_PrincipalObjectAccess]    Script Date: 8/14/2014 11:41:04 AM ******/
CREATE CLUSTERED INDEX [cndx_PrincipalObjectAccess] ON [dbo].[PrincipalObjectAccess]
(
	[ObjectId] ASC,
	[PrincipalId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

/****** Object:  Index [fndx_Sync_VersionNumber]    Script Date: 8/14/2014 11:41:04 AM ******/
CREATE UNIQUE NONCLUSTERED INDEX [fndx_Sync_VersionNumber] ON [dbo].[PrincipalObjectAccess]
(
	[VersionNumber] ASC
)
WHERE ([VersionNumber] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

/****** Object:  Index [ndx_Cover]    Script Date: 8/14/2014 11:41:04 AM ******/
CREATE NONCLUSTERED INDEX [ndx_Cover] ON [dbo].[PrincipalObjectAccess]
(
	[ObjectTypeCode] ASC,
	[PrincipalId] ASC,
	[AccessRightsMask] ASC,
	[InheritedAccessRightsMask] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
ALTER TABLE [dbo].[PrincipalObjectAccess] ADD  CONSTRAINT [DF_PrincipalObjectAccess_InheritedAccessRightsMask]  DEFAULT ((0)) FOR [InheritedAccessRightsMask]
ALTER TABLE [dbo].[PrincipalObjectAccess] ADD  CONSTRAINT [DF_PrincipalObjectAccess_PrincipalObjectAccessId]  DEFAULT (newid()) FOR [PrincipalObjectAccessId]

-- CLEAN UP - not necessary???
DROP Table [dbo].[ToDeletePoaEntries]
commit tran T1
PRINT 'EXECUTION SUCCEED'
update statistics PrincipalObjectAccess
END TRY

BEGIN CATCH
if @@trancount > 0
BEGIN 
 rollback tran T1
 PRINT 'TRANSACTION rolled back!!!!' 
END

IF EXISTS (SELECT * FROM sys.sysobjects
WHERE id = object_id(N'[dbo].[ToDeletePOAEntries]')
AND ObjectProperty(id, N'IsUserTable') = 1)
	DROP Table [dbo].[ToDeletePoaEntries]
PRINT 'EXECUTION FAILED :' + ERROR_MESSAGE()

END CATCH