Find all objects in Server Owned by a Login

Alter Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As
/*
Display all objects in all DBs owned by the Login.

2008-07-06 RBarryYoung Created.
2008-08-28 RBarryYoung Corrected of Windows vs SS default collations.
2008-12-06 RBarryYoung Fixed for spaces in DB names.
2009-06-01 TBanks Fixed for large quantities of DBs to avoid the 256-table union limit

Test:
spLogin_OwnedObjects 'sa'
*/
set nocount on

create table ##objectowners(
[DBID] int,
DBName varchar(255),
[Login] varchar(255),
[User] varchar(255),
name varchar(255),
[object_id] [int],
[principal_id] [int],
[schema_id] [int],
[parent_object_id] [int],
[type] [char](2),
[type_desc] [nvarchar](60),
[create_date] [datetime],
[modify_date] [datetime],
[is_ms_shipped] [bit],
[is_published] [bit],
[is_schema_published] [bit])

declare @sql varchar(MAX),
@DB_Objects varchar(512)
Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User], O.*
From [%D%].sys.objects o
Join [%D%].sys.database_principals u
ON Coalesce(o.principal_id, (Select S.Principal_ID from [%D%].sys.schemas S Where S.Schema_ID = O.schema_id))
= U.principal_id
left join [%D%].sys.server_principals L on L.sid = u.sid
'

Select @sql = 'insert ##objectowners SELECT * FROM
(Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '
+ Replace(@DB_objects, '%D%', [name])
From master.sys.databases
Where [name] = 'master'

select @sql = @sql + case when @Login is null then ') oo'
else ') oo Where Login = ''' + @Login + ''''
end
EXEC (@sql)

declare @db varchar(100),
@db_id int
DECLARE db_cursor CURSOR FOR
SELECT name, database_id
FROM master.sys.databases
where name <> 'master'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db, @db_id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'insert ##objectowners Select '+Cast(@db_id as varchar(9))+', '''+@db+''', '
+ Replace(@DB_objects, '%D%', @db)
select @sql = @sql + case when @Login is null then ' '
else ' Where L.name = ''' + @Login + ''''
end

exec (@sql)

FETCH NEXT FROM db_cursor INTO @db, @db_id
END
CLOSE db_cursor
DEALLOCATE db_cursor

select * from ##objectowners
drop table ##objectowners

set nocount off

tbanks-1094621 (12/4/2009)

Right you are. It works when you don't specify a login, but to specify a login, you'll need this:

Alter Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As
/*
  Display all objects in all DBs owned by the Login.
 
2008-07-06    RBarryYoung Created.
2008-08-28  RBarryYoung Corrected of Windows vs SS default collations.
2008-12-06    RBarryYoung Fixed for spaces in DB names.
2009-06-01    TBanks Fixed for large quantities of DBs to avoid the 256-table union limit
 
Test:
  spLogin_OwnedObjects 'sa'
*/
set nocount on

create table ##objectowners(
        [DBID] int,
        DBName varchar(255),
        [Login] varchar(255),
        [User] varchar(255),
        name varchar(255),
        [object_id] [int],
        [principal_id] [int],
        [schema_id] [int],
        [parent_object_id] [int],
        [type] [char](2),
        [type_desc] [nvarchar](60),
        [create_date] [datetime],
        [modify_date] [datetime],
        [is_ms_shipped] [bit],
        [is_published] [bit],
        [is_schema_published] [bit])

declare @sql varchar(MAX), 
        @DB_Objects varchar(512)
Select @DB_Objects = ' L.name  COLLATE DATABASE_DEFAULT as Login, U.Name  COLLATE DATABASE_DEFAULT as [User], O.*
        From [%D%].sys.objects o
        Join [%D%].sys.database_principals u
        ON Coalesce(o.principal_id, (Select S.Principal_ID from [%D%].sys.schemas S Where S.Schema_ID = O.schema_id))
        = U.principal_id
        left join [%D%].sys.server_principals L on L.sid = u.sid
        '

Select @sql = 'insert ##objectowners SELECT * FROM
        (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '
        + Replace(@DB_objects, '%D%', [name])
        From master.sys.databases
        Where [name] = 'master'

select @sql = @sql + case when @Login is null then ') oo'
                else ') oo Where Login = ''' + @Login + ''''
        end
EXEC (@sql)

declare @db varchar(100), 
        @db_id int
DECLARE db_cursor CURSOR FOR
        SELECT name, database_id
        FROM master.sys.databases
        where name <> 'master'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db, @db_id
        WHILE @@FETCH_STATUS = 0
        BEGIN
                SELECT @sql = 'insert ##objectowners Select '+Cast(@db_id as varchar(9))+', '''+@db+''', '
                        + Replace(@DB_objects, '%D%', @db)
                select @sql = @sql + case when @Login is null then ' '
                        else ' Where L.name = ''' + @Login + ''''
                        end

                exec (@sql)

                FETCH NEXT FROM db_cursor INTO @db, @db_id
        END
CLOSE db_cursor
DEALLOCATE db_cursor

select * from ##objectowners
drop table ##objectowners

set nocount off

create table #objectowners(
[DBID] int,
DBName varchar(255),
[Login] varchar(255),
[User] varchar(255),
name varchar(255),
[object_id] [int],
[principal_id] [int],
[schema_id] [int],
[parent_object_id] [int],
[type] [char](2),
[type_desc] [nvarchar](60),
[create_date] [datetime],
[modify_date] [datetime],
[is_ms_shipped] [bit],
[is_published] [bit],
[is_schema_published] [bit])

declare @sql varchar(MAX),
@DB_Objects varchar(512),
@login sysname

set @login = 'SomeName'

Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User], O.*
From [%D%].sys.objects o
Join [%D%].sys.database_principals u
ON Coalesce(o.principal_id, (Select S.Principal_ID from [%D%].sys.schemas S Where S.Schema_ID = O.schema_id))
= U.principal_id
left join [%D%].sys.server_principals L on L.sid = u.sid
'

set @sql = ''
Select @sql = @sql + 'insert #objectowners SELECT * FROM
(Select '+Cast(database_id as varchar(9))+' as DBID,''' + [Name] + ''' as DBName, '
+ Replace(@DB_objects, '%D%', [name]) + case when @Login is null then ') oo ;'
else ') oo Where Login like ''%' + @Login + '%''' end + ';'
From master.sys.databases

EXEC (@sql)
select * from #objectowners
drop table #objectowners

set nocount off