E venne il giorno… the database principal owns a schema in the database and cannot be dropped

E’ da qualche settimana che procastino la migrazione dei sistemi su SqlServer 2014 e questo weekend è risultato la scelta migliore anche grazie a all’accavallamento con il 1° maggio che ha garantito traffico zero (o quasi) sui nostri sistemi.

Spostare i DB non è di per se un grande problema, ma per ridurre al dba2minimo il tempo di “down” su alcuni servizi minori non ridondati, nel fare i test ho riscontrato qualche difficoltà nel rimappare schemi e utenti, che anche a parità di nome e password non corrispondono comunque una volta che il DB viene riconnesso.

Per evitare di ricollegare tanti piccoli DB a mano cambiano schemi e utenti, mi sono imbattuto in un’utilissima Stored Procedure che semplifica di molto la vita.

Infatti non basta “droppare” l’utente, perché se c’è uno schema collegato, l’operazione è ben più complicata e tediosa, e riceveremo errori del tipo “the database principal owns a schema in the database and cannot be dropped“.

Invece utilizzando questa stored procedure “Script to Drop All Orphaned SQL Server Database Users” l’operazione diventa estremamente rapita, e può essere lanciata su tutti i DB ricollegati in un colpo solo con il comando:

EXEC sp_msforeachdb 'USE [?]; EXEC sp_Drop_OrphanedUsers'

Un time saver eccezionale, se dovete fare una migrazione massiva.

Copio la stored qualora il sito dovesse scomparire:

use [master]
go
create proc dbo.sp_Drop_OrphanedUsers
as
begin
set nocount on
-- get orphaned users
declare @user varchar(max)
declare c_orphaned_user cursor for
select name
from sys.database_principals
where type in ('G','S','U')
and authentication_type<>2 -- Use this filter only if you are running on SQL Server 2012 and major versions and you have "contained databases"
and [sid] not in ( select [sid] from sys.server_principals where type in ('G','S','U') )
and name not in ('dbo','guest','INFORMATION_SCHEMA','sys','MS_DataCollectorInternalUser') open c_orphaned_user
fetch next from c_orphaned_user into @user
while(@@FETCH_STATUS=0)
begin
-- alter schemas for user
declare @schema_name varchar(max)
declare c_schema cursor for
select name from sys.schemas where USER_NAME(principal_id)[email protected]
open c_schema
fetch next from c_schema into @schema_name
while (@@FETCH_STATUS=0)
begin
declare @sql_schema varchar(max)
select @sql_schema='ALTER AUTHORIZATION ON SCHEMA::['[email protected]_name+ '] TO [dbo]'
print @sql_schema
exec(@sql_schema)
fetch next from c_schema into @schema_name
end
close c_schema
deallocate c_schema

-- alter roles for user
declare @dp_name varchar(max)
declare c_database_principal cursor for
select name from sys.database_principals
where type='R' and user_name(owning_principal_id)[email protected]
open c_database_principal
fetch next from c_database_principal into @dp_name
while (@@FETCH_STATUS=0)
begin
declare @sql_database_principal varchar(max)
select @sql_database_principal ='ALTER AUTHORIZATION ON ROLE::['[email protected]_name+ '] TO [dbo]'
print @sql_database_principal
exec(@sql_database_principal )
fetch next from c_database_principal into @dp_name
end
close c_database_principal
deallocate c_database_principal

-- drop roles for user
declare @role_name varchar(max)
declare c_role cursor for
select dp.name--,USER_NAME(member_principal_id)
from sys.database_role_members drm
inner join sys.database_principals dp
on dp.principal_id= drm.role_principal_id
where USER_NAME(member_principal_id)[email protected]
open c_role
fetch next from c_role into @role_name
while (@@FETCH_STATUS=0)
begin
declare @sql_role varchar(max)
select @sql_role='EXEC sp_droprolemember N'''[email protected]_name+''', N'''[email protected]+''''
print @sql_role
exec (@sql_role)
fetch next from c_role into @role_name
end
close c_role
deallocate c_role

-- drop user
declare @sql_user varchar(max)
set @sql_user='DROP USER ['[email protected] +']'
print @sql_user
exec (@sql_user)
fetch next from c_orphaned_user into @user
end
close c_orphaned_user
deallocate c_orphaned_user
set nocount off
end
go
-- mark stored procedure as a system stored procedure
exec sys.sp_MS_marksystemobject sp_Drop_OrphanedUsers
go