JoeSnyderJr
asked on
Error dropping shema
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.Conne ctionInfo)
Cannot drop schema 'EPIDIRECT\fmg.Jackie.Infu sino' 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?
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.Conne
Cannot drop schema 'EPIDIRECT\fmg.Jackie.Infu
(Microsoft SQL Server, Error: 3729)
Any suggestion how to determine dependency causing this problem and how to correct?
Look for this object: EPIDIRECT\fmg.Jackie.Infus ino.ds_Get ServerCase . 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.
ASKER
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
Right clicking on this schema does not show 'View Dependencies' as an option.
schema owner is dbo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.
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.