SQL Server 2005 unable to drop schema error: Cannot drop schema <> because it is being referenced by object
Recently I have received an email about unable to drop schemas that are adopted in the database design within an environment, here it goes.
I'm setting up an automatic build process that also includes building one or more databases. Initially it was decided to used separate databases for various parts of the solution, but after a bit of discussion we landed on using schemas instead. However, that causes problems for the build process as drop schema does not drop all corresponding objects (like drop database does). I naively tried the following sql-statement, but it fails of course with 'Ad hoc updates to system catalogs are not allowed':
delete from sys.all_objects
where object_id in (
select
ao.object_id
from
sys.all_objects ao
inner join
sys.schemas s
on
ao.schema_id = s.schema_id
and
s.name = 'plonk'
)
It is obvious with the above error that within SQL Server 2005 you will not be able to modify the system catalogs like it allowed in previous versions, not directly but there is a way to modify as per this http://sqlserver-qa.net/blogs/tools/archive/2007/04/24/sql-server-2005-ad-hoc-updates-to-system-catalogs-are-not-allowed.aspx blog post here, but never attempt to do so which is a bad practice.
Coming to the subject discussion as per the design of schemas in SQL 2005 note that you must first drop the table that is contained by the schema. And also the quote within BOL that:
In SQL Server 2005 the behavior of schemas is changed from the behavior in earlier versions of SQL Server. Code that assumes that schemas are equivalent to database users may not return correct results. Old catalog views, including sysobjects, should not be used in a database in which any of the following DDL statements has ever been used: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. In a database in which any of these statements has ever been used, you must use the new catalog views. The new catalog views take into account the separation of principals and schemas that is introduced in SQL Server 2005.
So as it referred you have to check what kind of owners are associated within that schema using the TSQL below:
SELECT s.name SchemaName, d.name SchemaOwnerName FROM sys.schemas s INNER JOIN sys.database_principals d ON s.principal_id= d.principal_id
To drop the relevant schema either you have to alter the schema owner to any other PRINCIPAL or change the ownership of a object to another schema by using the following TSQL:
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [db_datawriter]
or
ALTER SCHEMA Target_schema_name TRANSFER object_name
Further you can go through the BOL code examples under ALTER SCHEMA statement. Also ensure that you have scripted the permission of that original object involved in that schema before transferring to new schema, because the permissions granted to the original object will be removed during the trasfer to authorisation.