CODEWITHSUNDEEP
sql-serversql-server-2005user-management
Kolten
Kolten

Reputation: 3503

SQL 2005 cannot delete user

I have restored a database from a backup onto a new server, and I cannot delete the old schema from the database. I have created a NEW user for this database. The NEW user is named differently (to make things clearer for other developers), so I have no use for this schema. I DID manage to delete the USER finally, but the schema won't go away.

The error I receive is "Cannot drop schema '' because it is being referenced by object ''

Running this script:

select * from sys.objects where schema_id = 5

...shows all the objects this schema 'owns'.

My problem is I cannot find a way to change the ownership of these objects. There must be a way? Thanks all

Upvotes: 2

Views: 1402

Answers (1)

Andomar
Andomar

Reputation: 238086

The syntax to move object Thing from schema Source to schema Destination is:

ALTER SCHEMA Destination TRANSFER Source.Thing

Here's SQL to generate a script to transfer all objects to the dbo schema:

SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.tables
WHERE schema_id != SCHEMA_ID('dbo');

To just transfer one particular schema:

SELECT 'ALTER SCHEMA NewSchema TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.tables
WHERE schema_name(schema_id) = 'OldSchema'

Upvotes: 5

Related Questions