March 23, 2012

database inconsistency. Fix script for schema and principal id

-----DO NOT FORGET TO CHANGE schema ID -----
begin transaction

set nocount on

declare @SchemaToFix varchar(25)
set @SchemaToFix = 'prd'

declare @max int
declare @i int
declare @cmd varchar(500)
declare @ObjName varchar(500)
declare @ObjNameFull varchar(500)
declare @LastUserID int
declare @LastUser varchar(100)
declare @LastSchemaID int
declare @LastSchema varchar(100)
declare @CurrentUserID int
declare @CurrentSchemaID int

declare @MaxUserID int
declare @MaxSchemaID int

print convert(varchar(20),getdate(),109) + ' Starting ..'

if not exists (select * from sys.schemas where name = @SchemaToFix)
begin
print convert(varchar(20),getdate(),109) + ' Schema not found. Exiting.'
rollback transaction
return
end

if exists (select count(*) from sys.schemas
where principal_id = user_id(@SchemaToFix)
having count(*) > 1
) begin
print convert(varchar(20),getdate(),109) + ' User owns more than one schema. Exiting.'
print convert(varchar(20),getdate(),109) + ' See OSS note 1086375 for details.'
rollback transaction
return
end

if exists (select * from sys.schemas
where principal_id = user_id(@SchemaToFix)
and principal_id = schema_id
) begin
print convert(varchar(20),getdate(),109) + ' UserID and SchemaID are the same.'
print convert(varchar(20),getdate(),109) + ' Nothing to do. Exiting.'
rollback transaction
return
end

select @CurrentUserID = principal_id from sys.schemas where name = @SchemaToFix
select @CurrentSchemaID = schema_id from sys.schemas where name = @SchemaToFix

print convert(varchar(20),getdate(),109) + ' Current state: UserID = ' + convert(varchar(3), @CurrentUserID) + ' SchemaID = ' + convert(varchar(3), @CurrentSchemaID)

select @MaxUserID = max(uid) from sys.sysusers with (nolock) where uid < 16000
select @MaxSchemaID = max(schema_id) from sys.schemas with (nolock) where schema_id < 16000

if @MaxUserID > @MaxSchemaID
set @max = 5 + @MaxUserID
else
set @max = 5 + @MaxSchemaID

dbcc dropcleanbuffers
dbcc freeproccache

if exists (select top 1 name from sysusers where name like 'SchemaRepairU%') begin
declare TempUserCursor cursor for select name from sysusers where name like 'SchemaRepairU%'
open TempUserCursor
fetch next from TempUserCursor into @LastUser
while @@fetch_status = 0 begin
set @cmd = 'drop user ' + @LastUser
execute (@cmd)
fetch next from TempUserCursor into @LastUser
end
close TempUserCursor
deallocate TempUserCursor
end

if exists (select top 1 name from sys.schemas where name like 'SchemaRepairS%') begin
declare TempSchemaCursor cursor for select name from sys.schemas where name like 'SchemaRepairS%'
open TempSchemaCursor
fetch next from TempSchemaCursor into @LastSchema
while @@fetch_status = 0 begin
if not exists (select top 1 name from sysobjects where uid = schema_id(@LastSchema)) begin
set @cmd = 'drop schema ' + @LastSchema
execute (@cmd)
end
fetch next from TempSchemaCursor into @LastSchema
end
close TempSchemaCursor
deallocate TempSchemaCursor
end
print convert(varchar(20),getdate(),109) + ' Cleanup finished'

set @i = 1

set @LastUser = 'SchemaRepairU' + convert(varchar(3),@i)
set @cmd = 'create user ' + @LastUser + ' without login'
execute (@cmd)
set @i = @i + 1

select @LastUserID = uid from sysusers where name = @LastUser

while @LastUserID < @max - 1 begin
set @LastUser = 'SchemaRepairU' + convert(varchar(3),@i)
set @cmd = 'create user ' + @LastUser + ' without login'
execute (@cmd)
select @LastUserID = uid from sysusers where name = @LastUser
set @i = @i + 1
end
print convert(varchar(20),getdate(),109) + ' User created. LastUser is : ' + @LastUser
set @i = 1

set @LastSchema = 'SchemaRepairS' + convert(varchar(3),@i)
set @cmd = 'create schema ' + @LastSchema + ' '
execute (@cmd)
set @i = @i + 1

select @LastSchemaID = schema_id from sys.schemas where name = @LastSchema

while @LastSchemaID < @max - 1 begin
set @LastSchema = 'SchemaRepairS' + convert(varchar(3),@i)
set @cmd = 'create schema ' + @LastSchema + ' '
execute (@cmd)
select @LastSchemaID = schema_id from sys.schemas where name = @LastSchema
set @i = @i + 1
end

print convert(varchar(20),getdate(),109) + ' Schemas created. LastSchema is : ' + @LastSchema
print convert(varchar(20),getdate(),109) + ' Start moving objects .... '
--###########################################################################
declare TempObjectCursor cursor local for
select name from sysobjects where
((xtype='U' and name <> 'dtproperties')
or (xtype='V' and name not in ('syssegments','sysconstraints'))
or (xtype='P' and name not like 'dt_%')
or (xtype='D' and name not like 'DF__dtpropert%')
or (xtype in ('FN','TF','IF'))
) and uid = schema_id(@SchemaToFix)


open TempObjectCursor
fetch next from TempObjectCursor into @ObjName
while @@fetch_status=0
begin
set @ObjNameFull = '[' + @SchemaToFix + '].[' + @ObjName + ']'
set @cmd = N'ALTER SCHEMA ' + @LastSchema + ' TRANSFER ' + @ObjNameFull
exec( @cmd )
fetch next from TempObjectCursor into @ObjName
end

close TempObjectCursor
deallocate TempObjectCursor
--###########################################################################
print convert(varchar(20),getdate(),109) + ' All objects moved to schema ' + @LastSchema

set @cmd = 'alter authorization on schema::' + @LastSchema + ' to ' + @LastUser
execute (@cmd)
print convert(varchar(20),getdate(),109) + ' Authorization on schema ' + @LastSchema

set @cmd = 'alter authorization on schema::' + @SchemaToFix + ' to ' + @LastUser
execute (@cmd)
print convert(varchar(20),getdate(),109) + ' Authorization on schema ' + @SchemaToFix

set @cmd = 'drop schema ' + @SchemaToFix
execute (@cmd)
print convert(varchar(20),getdate(),109) + ' Schema dropped ' + @SchemaToFix

set @cmd = 'drop user ' + @SchemaToFix
execute (@cmd)
print convert(varchar(20),getdate(),109) + ' User dropped ' + @SchemaToFix

set @cmd = 'create user SchemaRepairU without login'
execute (@cmd)
print convert(varchar(20),getdate(),109) + ' User gap filled'

set @cmd = 'create schema SchemaRepairS'
execute (@cmd)
print convert(varchar(20),getdate(),109) + ' Schema gap filled'

set @cmd = 'create user ' + @SchemaToFix + ' for login ' + @SchemaToFix
execute (@cmd)
print convert(varchar(20),getdate(),109) + ' New schema user created'

set @cmd = 'create schema ' + @SchemaToFix
execute (@cmd)
print convert(varchar(20),getdate(),109) + ' New schema created'
print convert(varchar(20),getdate(),109) + ' Start moving objects back ..... '
--###########################################################################
declare TempObjectCursor cursor local for
select name
from sysobjects
where
( (xtype='U' and name <> 'dtproperties')
or (xtype='V' and name not in ('syssegments','sysconstraints'))
or (xtype='P' and name not like 'dt_%')
or (xtype='D' and name not like 'DF__dtpropert%')
or (xtype in ('FN','TF','IF'))
) and uid = schema_id(@LastSchema)

open TempObjectCursor
fetch next from TempObjectCursor into @ObjName
while @@fetch_status=0
begin
set @ObjNameFull = '[' + @LastSchema + '].[' + @ObjName + ']'
set @cmd = N'ALTER SCHEMA ' + @SchemaToFix + ' TRANSFER ' + @ObjNameFull
exec( @cmd )
fetch next from TempObjectCursor into @ObjName
end

close TempObjectCursor
deallocate TempObjectCursor
--###########################################################################
print convert(varchar(20),getdate(),109) + ' All objects moved into new schema'

set @cmd = 'alter authorization on schema::' + @SchemaToFix + ' to ' + @SchemaToFix
execute (@cmd)
set @cmd = 'alter user ' + @SchemaToFix + ' with default_schema = ' + @SchemaToFix
execute (@cmd)
exec sp_addrolemember 'db_owner', @SchemaToFix
print convert(varchar(20),getdate(),109) + ' Authorization set on new schema/user'

declare TempSchemaCursor cursor for select name from sys.schemas where name like 'SchemaRepairS%'
open TempSchemaCursor
fetch next from TempSchemaCursor into @LastSchema
while @@fetch_status = 0 begin
set @cmd = 'drop schema ' + @LastSchema
execute (@cmd)
fetch next from TempSchemaCursor into @LastSchema
end
close TempSchemaCursor
deallocate TempSchemaCursor
print convert(varchar(20),getdate(),109) + ' Final schema cleanup finished'

declare TempUserCursor cursor for select name from sysusers where name like 'SchemaRepairU%'
open TempUserCursor
fetch next from TempUserCursor into @LastUser
while @@fetch_status = 0 begin
set @cmd = 'drop user ' + @LastUser
execute (@cmd)
fetch next from TempUserCursor into @LastUser
end
close TempUserCursor
deallocate TempUserCursor
print convert(varchar(20),getdate(),109) + ' Final user cleanup finished'

commit transaction

exec sp_change_users_login 'update_one',@SchemaToFix,@SchemaToFix
print convert(varchar(20),getdate(),109) + ' New user accociated to the login.'

select @CurrentUserID = principal_id from sys.schemas where name = @SchemaToFix
select @CurrentSchemaID = schema_id from sys.schemas where name = @SchemaToFix

print convert(varchar(20),getdate(),109) + ' Current state: UserID = ' + convert(varchar(3), @CurrentUserID) + ' SchemaID = ' + convert(varchar(3), @CurrentSchemaID)

print convert(varchar(20),getdate(),109) + ' Script finished.'