can not drop user from database

I can not delete user from a database in sql2005 beta 3.
the message errror is :

TITLE: SQL Server Management Studio
-

Drop failed for User 'Amministratore'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.0981.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476

-
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

-

The database principal owns a schema and cannot be dropped. (Microsoft SQL Server, Error: 15138)

in sql 2000 I can delete the user very easy, but in sql 2005 I don't understant How to do it.

In SQL Server 2005, schemas are real entities. You cannot drop a user that owns schemas; you first have to either drop the schemas or change their owner to be another user.

Thanks
Laurentiu

|||

The previous answer is helpful. I am just elaborating for newbies in SQL 2005.

1) Expand Schemas(should be like a folder under <yourdatabase> -> Security) .
2) Delete the unwanted "userSchema".
3) Then, go back to the User(a folder like thing) and delete it.

|||

Hi,

I am having the same problem. When i went to delete the schema..i got the error message "drop failed for schema"

Cannot drop schema 'wch1' because it is being referenced by object 'Alert_List'. (.Net SqlClient Data Provider)

any suggestions?

|||

Before dropping a schema, it must be empty. Looks like in your case, you still have an object in the schema: Alert_List. You may either choose to drop this object first or you may choose to move it to another schema (using ALTER SCHEMA TRANSFER). When the schema will be empty, you will be able to drop it.

Thanks
Laurentiu

|||Is there any way i can tell what objects my schema has?|||

You can query the catalogs. For example, you can execute the following query:

select * from sys.objects where schema_id = schema_id('s')

to find out the objects that reside in schema 's'.

Thanks
Laurentiu

|||

I also had this problem.I was not able to find out which Schema that the login owned.I do not know of a stored proc function that will list ownership of schemas given an owner.Maybe someone can add that to this thread.

I was able to see which schemas were owned by the login by viewing the properties of each schema and seeing who what listed as the owner.

Example:

I opened the properties window of schema db_datareader and notices that the owner was User1.I changed the owner to be db_datareader and then was able to drop user1.

Regards,

DataSort

|||

Schemas are owned by users, not by logins. In SQL Server, logins and users are not the same thing.

To find out the schemas owned by a user, you can run the following query:

select*fromsys.schemaswhere principal_id =user_id('user_name')

Thanks
Laurentiu

|||Thank you!