Cannot drop the schema '', because it does not exist or you do not have permission. - SQL Server / TSQL Tutorial Part 29

Scenario:

You are working for Mortgage Company as SQL Server developer. You got this requirement to prepare SQL script to drop schema. You use
Drop Schema SchemeName 
statement in development SQL Server Instance and get below error.

Cannot drop the schema '', because it does not exist or you do not have permission.

What would be your next steps?

Solution:

There could be couple of reasons for above error. One, you really don't have permission to drop the Schema. You checked with DBA and he confirmed that you do have all permissions to take this change. Now you further looked into it and noticed that there are objects such as tables,views etc. which are using this schema.

From here you can propose two solution.

1) Drop all the Objects Related to Schema and then Drop the Schema (There could be scenarios where somebody was using this schema and objects. Now nobody need to use the schema and objects related to it anymore. So you will discuss with your team and if they say that prepare the drop script for objects and schema.You can use Object Explorer Detail windows to generate Drop Objects Scripts. 

2) You want to keep the objects and move them to different schema. Maybe dbo or any new schema. After transferring the objects, you want to delete schema.

Below Query can be used to generate script for Transferring objects.

Declare @SourceSchema VARCHAR(100)
Declare @DestinationSchema VARCHAR(100)
SET @SourceSchema='TB'
SET @DestinationSchema='dbo'
Select 'Alter Schema ['+@DestinationSchema+'] Transfer '+@SourceSchema+'.['+name+']' 
from sys.objects
where schema_name(schema_id)=@SourceSchema



Copy the generated scripts and then run in a database. Once done, Now you are good to drop the Schema by using Drop Schema SchemaName Statement.

1 comment:

  1. I liked your work and the way in which you have shared this article here about Mortgage Company for us .It is a beneficial and helpful article for us. Thanks for sharing an article like this.

    ReplyDelete