Determine Objects Owned by a SQL Login in all Databases

I was trying to delete a SQL login and received the following message:

"You cannot drop the selected ID because that login ID owns objects in one or more databases"

I wrote a little script that utilizes the always useful sp_MSForEachDB stored procedure to walk through each database and find the objects owned by a specified user.

SQL 2000

=======

--SQL Script begin

EXEC sp_MSForEachDB 'USE [?]; PRINT ''?''; select * from sysobjects where uid =
(select uid from sysusers where name = ''username'')'

--SQL Script end


SQL 2005

=======

--SQL Script begin

EXEC sp_MSForEachDB 'USE [?]; PRINT ''?''; select * from sys.objects where schema_id =

(select principal_id from sys.database_principals where name = ''username'')'

--SQL Script end

It is probably best to use CTL-T to have the output in text instead of grid format.

Script file for queries used in this article is here:

http://sites.google.com/a/karamazovgroup.com/blogfiles/Home/sql-server-scripts/Objectsownedbyauserinalldatabases.sql?attredirects=0

All SQL script files are here:

http://blogfiles.karamazovgroup.com/Home/sql-server-scripts