Cannot drop user(s) (designated as DB_OWNER)

Question

Cannot drop user(s) (designated as DB_OWNER)

Jpotucek

SSCoach

Points: 18583
More actions
November 10, 2008 at 7:25 am

#128517

I am in the procecss of migrating a Database from SQL Server 2000 to 2005.
In the past I have logged into my SQL Server 2000 Standard Edition Instance, scripted out the users associated with the database that I want to migrate then recreate the users on the 2005 Instance.
Then when I restore my 2000 DB to 2005, all users and their permissions are already in place.
This time I have a little bit of a different situation. The DB I want to move needs ALOT of clean-up!!! Seems the previous DBA made every NT authenticated login that has access to this database OWNER of the Database. What I WANTED to do in my test system is restore the database (2000 to 2005) then in the 2005 DB, drop all of the users so that I can create a DB role and add each user to the Role for their permissions.
My problem is, I cannot drop the users from my 2005 DB because I get this errror:
Msg 15138, Level 16, State 1, Line 3
The database principal owns a schema in the database, and cannot be dropped.
I know I'm getting this error because the user is designated as DB_Owner.
How can I get around this and drop the user? Is there a better way of doing this migration???

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

The users own SCHEMAS. Either change the owners of the schema objects to dbo, or drop the schema's (if they have no objects in them).

SCHEMAS get created automatically in the 2000 -> 2005 conversion process.

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 2

Jpotucek (11/10/2008)
My problem is, I cannot drop the users from my 2005 DB because I get this errror:
Msg 15138, Level 16, State 1, Line 3
The database principal owns a schema in the database, and cannot be dropped.
I know I'm getting this error because the user is designated as DB_Owner.
How can I get around this and drop the user? Is there a better way of doing this migration???

The problem is not that the user is member of the db_owner role, but because there is at least one schema which is owned by this user. Most likely you're looking for a schema with the same name as the username. Change the owner of the schema to some toher user g.e. "sa" .

ALTER AUTHORIZATION ON SCHEMA::[UserSchema] TO [sa]

GO

Once that's done you should be able to drop the user.

[font="Verdana"]Markus Bohse[/font]

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 3

Migrate the DB and then run sp_revlogin to transfer the logins.

As Earl said, you need to change the owner of the schemas to dbo and then delete the obsolete logins. Make sure that the logins to be deleted do not exist as users for any database.

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Jpotucek SSCoach Points: 18583 More actions · Answer 4

Thanks for all the suggestions. I don't have any obsolete logins, just trying to figure out the best way to migrate the DB and users to 2005 WHILE fixing the security mess. 40+ users all designated as DB-Owner..

It almost sound like it will be easier to fix all of the security problems while on 2000 and THEN migrate to 2005 ... any suggestions?

Kyle Neier , SSCrazy Points: 2252 More actions · Answer 5

When migrating from 2000 to 2005, SQL 2005 blindly makes the "safe" presumption and makes a new schema for every user and sets the default schema for that user to their particular schema. On every database I migrate, I run the attached script.

It will look at any users that do not map to the "dbo" schema and create a script to alter their default schema as well as drop the system created schema if there are no objects owned by it. If there are owned objects, it does nothing, and you'll need to change the owner of the schema -- as Markus suggested.

Since all the users were db_owner, I'll suggest it is very unlikely that any of them used any name space outside of DBO, so this should clean up your mappings so you can cleanly drop the users.

Kyle

SELECT

'USE [' + db_name() + ']; ' + CHAR(10) + 'IF EXISTS(SELECT name FROM sys.schemas where name=' + CHAR(39) + default_schema_name + CHAR(39) + ') ' + CHAR(10) + 'BEGIN' + CHAR(10) + ' IF NOT EXISTS(SELECT schema_id FROM sys.all_objects WHERE schema_name(schema_id) = ' + CHAR(39) + default_schema_name + CHAR(39) + ')' + CHAR(10) + ' BEGIN' + CHAR(10) + 'ALTER USER [' + name + '] WITH DEFAULT_SCHEMA=[dbo]' + CHAR(10) + 'DROP SCHEMA [' + default_schema_name + ']' + CHAR(10) + ' END' + CHAR(10) + ' END;' + CHAR(10)

FROM

sys.database_principals

where

type in ('S', 'U')

and name <> 'guest'

and default_schema_name <> 'dbo'

Jpotucek SSCoach Points: 18583 More actions · Answer 6

Thank you so much! It's all starting to make sense now.. I guess I really didn't understand that it was creating a schema for each user in the database.