Delete Database User w/o Login name

I have a database user (Maximum). Its default schema is db_datareader. It was created without a login name. When I try and delete this user, i get the following error message:

Drop failed for User 'maximum'. (Microsoft.SqlServer.Smo)
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

How do I remove this user?

It seems like your user owns some schema, most likely the default schema that gets created for the user. You will need to use the "DROP SCHEMA" statement to first drop the user's schema (if it is the default schema, the schema's name will most likely be the same as the user's name). Otherwise you can use "ALTER AUTHORIZATION" to change to the owner of the schema to someone else if you want to keep it. After that you should be able to drop the user.

Thanks,

Sung

|||

You can use the following query to help you identify the schema you need to drop.

select schm.name as 'schema_name'

FROM sys.schemas schm, sys.database_principals usrs

WHERE schm.principal_id = usrs.principal_id AND usrs.name = 'dbo'

-Raul Garcia

SDE/T

SQL Server Engine