SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by RBarryYoung / Find all objects in Server Owned by a Login / Latest Posts

RE: Find all objects in Server Owned by a Login

Preet_S — Fri, 16 Sep 2011 10:58:03 GMT

[quote][b]tbanks-1094621 (12/4/2009)[/b][hr]Right you are. It works when you don't specify a login, but to specify a login, you'll need this:[code]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 oncreate 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 + '''' endEXEC (@sql)declare @db varchar(100), @db_id intDECLARE db_cursor CURSOR FOR SELECT name, database_id FROM master.sys.databases where name <> 'master'OPEN db_cursorFETCH 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 ENDCLOSE db_cursorDEALLOCATE db_cursorselect * from ##objectownersdrop table ##objectownersset nocount off[/code][/quote]@RBarryYoung - Nifty bit of code.@tbanks-1094621, good effort but you dont need a cursor for this, neither do you need a global temp table.Try this (I have changed it from creating a sproc to a standalone script, just change "set @login =" to something appropriate) ; [code]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 sysnameset @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 #objectownersdrop table #objectownersset nocount off[/code]

RE: Find all objects in Server Owned by a Login

chumphrey 12211 — Thu, 26 Aug 2010 14:44:54 GMT

Well, turns out I did still have an issue. We have Logins like Corp/Jsmith. If I run the latest procedure and enter Corp/Jsmith I get an error. If I run it with [Corp/Jsmith] it works. Just wanted to pass this on. I thought I read that code was added to fix this?

RE: Find all objects in Server Owned by a Login

chumphrey 12211 — Thu, 26 Aug 2010 13:50:36 GMT

Sorry, I said I was new. Didn't see the additional pages with the latest version. Thanks. I have no errors.

RE: Find all objects in Server Owned by a Login

chumphrey 12211 — Thu, 26 Aug 2010 13:42:14 GMT

I am a newbie as well! I had over 256 tables too so I tried this last modified version and I am getting the following errors?Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Login'.

RE: Find all objects in Server Owned by a Login

JC-3113 — Wed, 21 Apr 2010 11:37:35 GMT

Anybodydo we have a correct, working version of this scriptther have been hanges, and i cannot get it to workit would be nice if we had a working copy some where pleaseThanksJim

RE: Find all objects in Server Owned by a Login

JC-3113 — Fri, 04 Dec 2009 11:41:53 GMT

Well...it is geting thereat least no errorsbut i have names like this: LOSANGELES-2K\cox.jim.admEXEC spLogin_OwnedObjects 'LOSANGELES-2K\cox.jim.adm'it returns nothing and i know there are objects there as i was getting them before the 256 limitany ideas ?ThaksJim

RE: Find all objects in Server Owned by a Login

tbanks-1094621 — Fri, 04 Dec 2009 11:30:26 GMT

Right you are. It works when you don't specify a login, but to specify a login, you'll need this:[code]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 oncreate 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 + '''' endEXEC (@sql)declare @db varchar(100), @db_id intDECLARE db_cursor CURSOR FOR SELECT name, database_id FROM master.sys.databases where name <> 'master'OPEN db_cursorFETCH 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 ENDCLOSE db_cursorDEALLOCATE db_cursorselect * from ##objectownersdrop table ##objectownersset nocount off[/code]

RE: Find all objects in Server Owned by a Login

JC-3113 — Fri, 04 Dec 2009 11:03:16 GMT

SorryMsg 207, Level 16, State 1, Server NSAB-SS83-SQL-N, Line 7Invalid column name 'Login'.Msg 207, Level 16, State 1, Server NSAB-SS83-SQL-N, Line 7Invalid column name 'Login'.Jim

RE: Find all objects in Server Owned by a Login

tbanks-1094621 — Fri, 04 Dec 2009 10:35:02 GMT

[code]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 oncreate 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 + '''' endEXEC (@sql)declare @db varchar(100), @db_id intDECLARE db_cursor CURSOR FOR SELECT name, database_id FROM master.sys.databases where name <> 'master'OPEN db_cursorFETCH 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 Login = ''' + @Login + '''' end exec (@sql) FETCH NEXT FROM db_cursor INTO @db, @db_id ENDCLOSE db_cursorDEALLOCATE db_cursorselect * from ##objectownersdrop table ##objectownersset nocount off[/code]

RE: Find all objects in Server Owned by a Login

JC-3113 — Fri, 04 Dec 2009 10:29:24 GMT

Hi RBarry Youngare yiou sureit is still not workingi copied the code from aboveThanksJim

RE: Find all objects in Server Owned by a Login

tbanks-1094621 — Fri, 04 Dec 2009 07:03:34 GMT

If you go back in the thread to my earlier reply with code, it has the brackets issue and the 256-table issue fixed. Hope that helps!

RE: Find all objects in Server Owned by a Login

RBarryYoung — Thu, 03 Dec 2009 23:39:24 GMT

[quote][b]JC-3113 (12/3/2009)[/b][hr]Hi RBarry Youngsorry to bug you again, but i came across another issue when running this on another serverseems there's a 256 limit somewhereMsg 106, Level 15, State 1, Server NSAB-SS83-SQL-N, Line 1218Too many table names in the query. The maximum allowable is 256.Msg 102, Level 15, State 1, Server NSAB-SS83-SQL-N, Line 1234Incorrect syntax near ')'.Msg 102, Level 15, State 1, Server NSAB-SS83-SQL-N, Line 1256Incorrect syntax near ')'.ThanksJiim[/quote]Hmm, the 256-db/table limit is a tougher nut to crack and I am afraid that I will not be able to address it right away...

RE: Find all objects in Server Owned by a Login

JC-3113 — Thu, 03 Dec 2009 14:10:52 GMT

Hi RBarry Youngsorry to bug you again, but i came across another issue when running this on another serverseems there's a 256 limit somewhereMsg 106, Level 15, State 1, Server NSAB-SS83-SQL-N, Line 1218Too many table names in the query. The maximum allowable is 256.Msg 102, Level 15, State 1, Server NSAB-SS83-SQL-N, Line 1234Incorrect syntax near ')'.Msg 102, Level 15, State 1, Server NSAB-SS83-SQL-N, Line 1256Incorrect syntax near ')'.ThanksJiim

RE: Find all objects in Server Owned by a Login

JC-3113 — Thu, 03 Dec 2009 14:00:12 GMT

Well at least it was obvious to you and you fixed it quicklythanks againjim

RE: Find all objects in Server Owned by a Login

RBarryYoung — Thu, 03 Dec 2009 13:42:49 GMT

Unquoted names (i.e., no "[..]") cannot have certain characters in them like spaces or "-". So I just added the brackets around the database names ("%D%" --> "[%D%]").Thing is, I thought that I had done that a long time ago...

RE: Find all objects in Server Owned by a Login

JC-3113 — Thu, 03 Dec 2009 13:34:38 GMT

Excellent RBarryYoungit was not obvious to me apparently.. what was it ?ThanksJim

RE: Find all objects in Server Owned by a Login

RBarryYoung — Thu, 03 Dec 2009 13:29:24 GMT

Hmmm, weird. It's obvious what's wrong, but I though that I had fixed it long ago. Well, this should fix it anyway:[code]ALTER Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As/* Display all objects in all DBs owned by the Login.2008-07-06 RBarryYoung Created.Test: EXEC spLogin_OwnedObjects 'sa'*/ declare @sql varchar(MAX), @DB_Objects varchar(MAX) Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published 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 = '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 + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', ' + Replace(@DB_objects, '%D%', [name]) From master.sys.databases Where [name] != 'master' Select @sql = @sql + ') oo Where Login = ''' + @Login + '''' print @sql EXEC (@sql)[/code]

RE: Find all objects in Server Owned by a Login

JC-3113 — Thu, 03 Dec 2009 13:04:07 GMT

Hi RBarryYoungstill having issues:E:\SQL_Server_SQL>sqlcmd1> --http://www.sqlservercentral.com/scripts/Administration/63631/2> --Find all objects in Server Owned by a Login3> --By RBarry Young, 2008/08/284>5>6> CREATE Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As7> /*8~ Display all objects in all DBs owned by the Login.9~10~ 2008-07-06 RBarryYoung Created.11~ 2008-08-28 RBarryYoung Added corrections for DBs with different Collations12~ (note that ReportingDBs have different Collations)13~14~ Test:15~ EXEC spLogin_OwnedObjects 'sa'16~ */17> declare @sql varchar(MAX), @DB_Objects varchar(MAX)18> Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]19~ , o.name COLLATE DATABASE_DEFAULT as [name]20~ , o.object_id21~ , o.principal_id22~ , o.schema_id23~ , o.parent_object_id24~ , o.type COLLATE DATABASE_DEFAULT as [type]25~ , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]26~ , o.create_date27~ , o.modify_date28~ , o.is_ms_shipped29~ , o.is_published30~ , o.is_schema_published31~ From %D%.sys.objects o32~ Join %D%.sys.database_principals u33~ ON Coalesce(o.principal_id34~ , (Select S.Principal_ID from %D%.sys.schemas S Where S.Schema_ID = O.schema_id))35~ = U.principal_id36~ Left Join %D%.sys.server_principals L on L.sid = u.sid37~ '38>39> Select @sql = 'SELECT * FROM40~ (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '41> + Replace(@DB_objects, '%D%', [name])42> From master.sys.databases43> Where [name] = 'master'44>45> Select @sql = @sql + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '46> + Replace(@DB_objects, '%D%', [name])47> From master.sys.databases48> Where [name] != 'master'49>50> Select @sql = @sql + ') oo Where Login = ''' + @Login + ''''51>52> print @sql53> EXEC (@sql)54> go1>2> EXEC spLogin_OwnedObjects 'sa'3> goSELECT * FROM (Select 1 as DBID, 'master' as DBName, L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From master.sys.objects o Join master.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from master.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join master.sys.server_principals L on L.sid = u.sidUNION ALL Select 9, 'AdventureWorks', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From AdventureWorks.sys.objects o Join AdventureWorks.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from AdventureWorks.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join AdventureWorks.sys.server_principals L on L.sid = u.sidUNION ALL Select 8, 'AdventureWorksDW', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From AdventureWorksDW.sys.objects o Join AdventureWorksDW.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from AdventureWorksDW.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join AdventureWorksDW.sys.server_principals L on L.sid = u.sidUNION ALL Select 10, 'AdventureWorksLT', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From AdventureWorksLT.sys.objects o Join AdventureWorksLT.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from AdventureWorksLT.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join AdventureWorksLT.sys.server_principals L on L.sid = u.sidUNION ALL Select 3, 'model', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From model.sys.objects o Join model.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from model.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join model.sys.server_principals L on L.sid = u.sidUNION ALL Select 4, 'msdb', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From msdb.sys.objects o Join msdb.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from msdb.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join msdb.sys.server_principals L on L.sid = u.sidUNION ALL Select 14, 'NorthWind', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From NorthWind.sys.objects o Join NorthWind.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from NorthWind.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join NorthWind.sys.server_principals L on L.sid = u.sidUNION ALL Select 20, 'QA_SharedServices1_DB', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From QA_SharedServices1_DB.sys.objects o Join QA_SharedServices1_DB.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from QA_SharedServices1_DB.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join QA_SharedServices1_DB.sys.server_principals L on L.sid = u.sidUNION ALL Select 21, 'QA_SharedServices1_Search_DB', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From QA_SharedServices1_Search_DB.sys.objects o Join QA_SharedServices1_Search_DB.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from QA_SharedServices1_Search_DB.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join QA_SharedServices1_Search_DB.sys.server_principals L on L.sid = u.sidUNION ALL Select 7, 'QA_SharePoint_Config', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From QA_SharePoint_Config.sys.objects o Join QA_SharePoint_Config.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from QA_SharePoint_Config.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join QA_SharePoint_Config.sys.server_principals L on L.sid = u.sidUNION ALL Select 17, 'QA_WSS_Content_Home', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.oMsg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 262Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 265Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 281Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 284Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 338Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 341Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 357Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 360Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 376Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 379Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 724Incorrect syntax near ')'.1>

RE: Find all objects in Server Owned by a Login

tbanks-1094621 — Tue, 02 Jun 2009 09:57:54 GMT

Hope you don't mind, I found the guts of this quite useful but modified it in a couple of ways:1. First, I've got enough databases on the server in question that I was running into a 256-table union limit while attempting to run it the way it was.2. Secondly, I'd like on occasion to see all owners of all objects. A null @Login will return this result.[code]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 oncreate 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 + '''' endEXEC (@sql)declare @db varchar(100), @db_id intDECLARE db_cursor CURSOR FOR SELECT name, database_id FROM master.sys.databases where name <> 'master'OPEN db_cursorFETCH 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 Login = ''' + @Login + '''' end exec (@sql) FETCH NEXT FROM db_cursor INTO @db, @db_id ENDCLOSE db_cursorDEALLOCATE db_cursorselect * from ##objectownersdrop table ##objectownersset nocount off[/code]

RE: Find all objects in Server Owned by a Login

RBarryYoung — Sun, 28 Dec 2008 13:57:56 GMT

Glad I could help.

RE: Find all objects in Server Owned by a Login

Lucien Grieten — Sun, 28 Dec 2008 11:31:34 GMT

Thanks very much, this is working fine now, really appreciate your answerLucien

RE: Find all objects in Server Owned by a Login

RBarryYoung — Sat, 06 Dec 2008 19:07:47 GMT

Looks like you have spaces in your database names, something that I did not take into account. Try this new version and let me know if it works.[code]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.Test: spLogin_OwnedObjects 'sa'*/ 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 = '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 + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', ' + Replace(@DB_objects, '%D%', [name]) From master.sys.databases Where [name] != 'master' Select @sql = @sql + ') oo Where Login = ''' + @Login + '''' print @sql EXEC (@sql)[/code]

RE: Find all objects in Server Owned by a Login

Lucien Grieten — Sat, 06 Dec 2008 01:03:20 GMT

Hello, I am a newbie to sql server and I have created the sp in MasterWhen I execute the sp using 'sa' as parameter the following error occurs: Can you help?SELECT * FROM (Select 1 as DBID, 'master' as DBName, L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From master.sys.objects o Join master.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from master.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join master.sys.server_principals L on L.sid = u.sidUNION ALL Select 7, 'APS CRP PS', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From APS CRP PS.sys.objects o Join APS CRP PS.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from APS CRP PS.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join APS CRP PS.sys.server_principals L on L.sid = u.sidUNION ALL Select 8, 'APS CRP PS TEST', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From APS CRP PS TEST.sys.objects o Join APS CRP PS TEST.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from APS CRP PS TEST.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join APS CRP PS TEST.sys.server_principals L on L.sid = u.sidUNION ALL Select 9, 'APS Shortterm planning', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From APS Shortterm planning.sys.objects o Join APS Shortterm planning.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from APS Shortterm planning.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join APS Shortterm planning.sys.server_principals L on L.sid = u.sidUNION ALL Select 3, 'model', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From model.sys.objects o Join model.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from model.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join model.sys.server_principals L on L.sid = u.sidUNION ALL Select 4, 'msdb', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From msdb.sys.objects o Join msdb.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from msdb.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join msdb.sys.server_principals L on L.sid = u.sidUNION ALL Select 6, 'MWareArc', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From MWareArc.sys.objects o Join MWareArc.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from MWareArc.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join MWareArc.sys.server_principals L on L.sid = u.sidUNION ALL Select 5, 'MWarePrd', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From MWarePrd.sys.objects o Join MWarePrd.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from MWarePrd.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join MWarePrd.sys.server_principals L on L.sid = u.sidUNION ALL Select 2, 'tempdb', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From tempdb.sys.objects o Join tempdb.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from tempdb.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join tempdb.sys.server_principals L on L.sid = u.sid) oo Where Login = 'sa'Msg 102, Level 15, State 1, Line 34Incorrect syntax near 'PS'.Msg 102, Level 15, State 1, Line 37Incorrect syntax near 'PS'.Msg 102, Level 15, State 1, Line 53Incorrect syntax near 'PS'.Msg 102, Level 15, State 1, Line 56Incorrect syntax near 'PS'.Msg 102, Level 15, State 1, Line 72Incorrect syntax near 'planning'.Msg 102, Level 15, State 1, Line 75Incorrect syntax near 'planning'.Msg 102, Level 15, State 1, Line 173Incorrect syntax near ')'.

RE: Find all objects in Server Owned by a Login

RBarryYoung — Thu, 28 Aug 2008 13:14:38 GMT

Yep, they are that way on my SQL 2008 box also.Well here is a corrected and tested version that should work for all of the databases, including the Reporting DBs:[code]ALTER Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As/* Display all objects in all DBs owned by the Login.2008-07-06 RBarryYoung Created.Test: EXEC spLogin_OwnedObjects 'sa'*/ declare @sql varchar(MAX), @DB_Objects varchar(MAX) Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published 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 = '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 + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', ' + Replace(@DB_objects, '%D%', [name]) From master.sys.databases Where [name] != 'master' Select @sql = @sql + ') oo Where Login = ''' + @Login + '''' print @sql EXEC (@sql)[/code]Thanks for pointing this out to me Mark. I will get the corrections up to the article ASAP.

RE: Find all objects in Server Owned by a Login

Mark D Powell — Thu, 28 Aug 2008 11:49:14 GMT

All my databases are SQL_Latin1_General_CP1_CI_AS which as far as I know was the SQL Server 2000 default.I am pretty sure that when I installed SQL Server 2005 that I took the defaults. The ReportServer databases have a collation of Latin1_General_CI_AS_KS_WS.You fix still errored off so what I did was to add and [name] != 'ReportServer' and [name] != 'ReportServerTempDB' to the second union's query.That seems to have gotten around the issue.Anyone know if the ReportServer settings are normal?-- Mark D Powell --

RE: Find all objects in Server Owned by a Login

RBarryYoung — Thu, 28 Aug 2008 11:21:08 GMT

Hmm, thats a new one on me. Of course I hardly ever get to work with DB's with different collations. Try this altered version and let me know if it works:[code]Alter Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As/* Display all objects in all DBs owned by the Login.2008-07-06 RBarryYoung Created.Test: spLogin_OwnedObjects 'sa'*/ 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 = '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 + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', ' + Replace(@DB_objects, '%D%', [name]) From master.sys.databases Where [name] != 'master' Select @sql = @sql + ') oo Where Login = ''' + @Login + '''' print @sql EXEC (@sql)[/code]If you still get the second error, try taking out the "o.*" and see if it will run without error (if so, then I will need to make a large change).

RE: Find all objects in Server Owned by a Login

Mark D Powell — Thu, 28 Aug 2008 10:58:06 GMT

I get errors when I try to run the code using Standard Edition with SP2 applied.SELECT * FROM (Select 1 as DBID, 'master' as DBName, L.name as Login, U.Name as [User], O.* From master.sys.objects o Join master.sys.database_principals u ON Coalesce(o.principal_id, (Select S.Principal_ID from master.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id left join master.sys.server_principals L on L.sid = u.sid UNION ALL Select 11, 'E4BOTTLELABEL', L.name as Login, U.Name as [User], O.* From E4BOTTLELABEL.sys.objects o Join E4BOTTLELABEL.sys.database_principals u ON Coalesce(o.principal_id, (Select S.Principal_ID from E4BOTTLELABEL.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id left join E4BOTTLELABEL.sys.server_principals L on L.sid = u.sid UNION ALL Select 7, 'E4COMMON', L.name as Login, U.Name as [User], O.* From E4COMMON.sys.objects o Join E4COMMON.sys.database_principals u ON Coalesce(o.principal_id, (Select S.Principal_ID from E4COMMON.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id left join E4COMMON.sys.server_principals L on L.sid = u.sid UNION ALL Select 8, 'E4ENGINECERT', L.name as Login, U.Name as [User], O.* From E4ENGINECERT.sys.objects o Join E4ENGINECERT.sys.database_principals u ON Coalesce(o.principal_id, (Select S.Principal_ID from E4ENGINECERT.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id left join E4ENGINECERT.sys.server_principals L on L.sid = u.sid UNION ALL Select 9, 'E4ENGINEUTIL', L.name as Login, U.Name as [User], O.* From E4ENGINEUTIL.sys.objects o Join E4ENGINEUTIL.sys.database_principals u ON Coalesce(o.principal_id, (Select S.Principal_ID from E4ENGINEUTIL.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id left join E4ENGINEUTIL.sys.server_principals L on L.sid = u.sid UNION ALL Select 10, 'E4TESTREQUEST', L.name as Login, U.Name as [User], O.* From E4TESTREQUEST.sys.objects o Join E4TESTREQUEST.sys.database_principals u ON Coalesce(o.principal_id, (Select S.Principal_ID from E4TESTREQUEST.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id left join E4TESTREQUEST.sys.server_principals L on L.sid = u.sid UNION ALL Select 12, 'ELAB', L.name as Login, U.Name as [User], O.* From ELAB.sys.objects o Join ELAB.sys.database_principals u ON Coalesce(o.principal_id, (Select S.Principal_ID from ELAB.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id left join ELAB.sys.server_principals L on L.sid = u.sid UNION ALL Select 3, 'model', L.name as Login, U.Name as [User], O.* From model.sys.objects o Join model.sys.database_principals u ON Coalesce(o.principal_id, (Select S.Principal_ID from model.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id left join model.sys.server_principals L on L.sid = u.sid UNION ALL Select 4, 'msdb', L.name as Login, U.Name as [User], O.* From msdb.sys.objects o Join msdb.sys.database_principals u ON Coalesce(o.principal_id, (Select S.Principal_ID from msdb.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id left join msdb.sys.server_principals L on L.sid = u.sid UNION ALL Select 5, 'ReportServer', L.name as Login, U.Name as [User], O.* From ReportServer.sys.objects o Join ReportServer.sys.database_principals u ON Coalesce(o.principal_id, (Select S.Principal_ID from ReportServer.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id left join ReportServer.sys.server_principals L on L.sid = u.sid UNION ALL Select 6, 'ReportServerTempDB', L.name as Login, U.Name as [User], O.* From ReportServerTempDB.sys.objects o Join ReportServerTempDB.sys.database_principals u ON Coalesce(o.principal_id, (Select S.Principal_ID from ReportServerTempDB.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id left join ReportServerTempDB.sys.server_principals L on L.sid = u.sid UNION ALL Select 2, 'tempdb', L.name as Login, U.Name as [User], O.* From tempdb.sys.objects o Join tempdb.sys.database_principals u ON Coalesce(o.principal_id, (Select S.Principal_ID from tempdb.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id left join tempdb.sys.server_principals L on L.sid = u.sid ) oo Where Login = 'sa'Msg 451, Level 16, State 1, Line 1Cannot resolve collation conflict for column 4 in SELECT statement.Msg 451, Level 16, State 1, Line 1Cannot resolve collation conflict for column 5 in SELECT statement.I was going to try expaning the O.* column list but collation conflict seems like it could be indicating a different problem that resolving the table names correctly.

Find all objects in Server Owned by a Login

RBarryYoung — Mon, 07 Jul 2008 23:08:00 GMT

Comments posted to this topic are about the item [B]Find all objects in Server Owned by a Login[/B]