Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment