Error trying to delete database user account schema

Question

I'm trying to delete a user's schema from a database and I'm getting the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop failed for Schema 'ext_owner'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Schema&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot drop schema 'ext_owner' because it is being referenced by object 'getroles'. (Microsoft SQL Server, Error: 3729)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=3729&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

What is the getroles object?

How do I get rid of the reference so I can remove the old user account?

Answer 1

You can query the system table sys.objects to try to find more information on what getroles might be.

Try

SELECT * FROM sys.objects WHERE name LIKE '%getroles%'

This should give you some information on what the object is. At that point you can either drop it or decide to change the schema so that it's no longer being referenced.

Answer 2

Try using:

SELECT * FROM sys.objects WHERE name = 'getroles'

My guess is that it is a function or stored procedure which that user created under their own schema (possibly accidentally. Drop it (if it's not being used) and you should be good to go.

Answer 3

SELECT * FROM sys.objects 
WHERE name = 'getroles' 
AND schema_id = SCHEMA_ID('ext_owner');

Then do:

DROP <object type> ext_owner.getroles;

--or

ALTER SCHEMA <some other schema> TRANSFER ext_owner.getroles;

You will likely have to repeat this a bunch of times. You can't drop a schema that is not empty.