Error dropping shema

JoeSnyderJr
JoeSnyderJr used Ask the Experts™
on
Attempting to drop shema then user via MSSQL 2005 Server Mngr but get the following error.
I do know this user account is not logged in when this error occurs.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot drop schema 'EPIDIRECT\fmg.Jackie.Infusino' because it is being referenced by object 'ds_GetServerCase'.
(Microsoft SQL Server, Error: 3729)

Any suggestion how to determine dependency causing this problem and how to correct?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Faiga DiegelSr Database Engineer

Commented:
Look for this object: EPIDIRECT\fmg.Jackie.Infusino.ds_GetServerCase. This maybe an SP or table or view or function. Change that object to another owner -- dbo perhaps, then try droppign the schema again.
Right click the schema and thru Studio Management and click View Dependecies.

Author

Commented:
ds_GetServerCase is owned by dbo with no dependencies

Right clicking on this schema does not show 'View Dependencies' as an option.

schema owner is dbo
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

This query will generate script to change schema name for all the tables:

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name FROM sys.Tables p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = 'oldschemaname'

this second is for stored procedures:

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name FROM sys.Procedures p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = 'oldschemaname'

First you will alter all these objects and after this delete the schema.

Author

Commented:
Ran this script, then ran following script. All dependences seem to still exist. Attempted schema delete with same error

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name FROM sys.Procedures p INNER JOIN sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name <> 'dbo'

SELECT s.Name + '.' + p.Name, * FROM sys.Procedures p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id
WHERE s.Name <> 'dbo'

Not sure if could be relevant but each of the users are also members of a group with dbo priviledges.
Execute the scripts that the previus query will generate. So that all the procedures will change the schema to dbo.

Author

Commented:
I did run these scripts and attempted to delete the schema but continued to receive the dependency error.
However I did find where an application was creating a stored procedure for each login account. Was able to delete these procedures and then successfully remove the schemas in question.

Given this suggestion helped me to find problem but not convinced it fixed I am awarding partial points.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial