Description of the problem: When you tried to drop a user, you got this message:Error: 15138 The database principal owns a schema in the database, and cannot be dropped.
Cause: That means, you are trying to drop a user owning a schema. In order to drop the user, you have to find the schema that’s assigned and then transfer the ownership to another user/role or to drop it.
Resolution: You can fix the issue following two ways.By script: You can find out which schema is owned by this user with the query below:SELECT name FROM sys.schemas WHERE principal_id = USER_ID('myUser')Then, use the names found from the above query below in place of the SchemaName below. And drop your user. ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo GODROP USER myUser
By Management Studio:
- Object Explorer >> Expand the [databasename] >> Security. - Click on Schemas. - In summary window, determine which Schema(s) are owned by the user and either change the owner or remove the Scheme(s).- If they are system schema(s), I suggest to change them to ‘dbo’.- Drop your user.
More detail about schemas into the BOL:http://msdn2.microsoft.com/en-us/library/ms190387.aspx
Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |