[SOLVED] Generalized query to find whether the database user owns a schema

Generalized query to find whether the database user owns a schema

We have a lot of DBUsers in our database. We have to clean up all those users from the database. When I tried to drop the user from the DB it failed due to the following error

Msg 15138, Level 16, State 1, Line 2 The database principal owns a schema in the database, and cannot be dropped.

So I found the solution for this i.e I changed the ownership of the schema to dbo. Now I can drop the user by using below script

SELECT name FROM  sys.schemas WHERE principal_id = USER_ID('myUser')
ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo 
GO
DROP USER myUser

But I want to drop a number of users so I need to generate generalized script. I am using SQL Server 2008

Solution

This will give you a complete list of schemas against the users which own them:

SELECT db.name AS [DB User], s.name AS [Schema]
FROM sys.database_principals db
JOIN sys.schemas s ON s.principal_id = db.principal_id