Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Find all objects in Server Owned by a Login Expand / Collapse
Author
Message
Posted Monday, July 7, 2008 11:08 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
Comments posted to this topic are about the item Find all objects in Server Owned by a Login

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #529761
Posted Thursday, August 28, 2008 10:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:55 PM
Points: 1,390, Visits: 409
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 1
Cannot resolve collation conflict for column 4 in SELECT statement.
Msg 451, Level 16, State 1, Line 1
Cannot 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.

Post #560681
Posted Thursday, August 28, 2008 11:21 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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:
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)

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).


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #560694
Posted Thursday, August 28, 2008 11:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:55 PM
Points: 1,390, Visits: 409
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 --
Post #560712
Posted Thursday, August 28, 2008 1:14 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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:
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)

Thanks for pointing this out to me Mark. I will get the corrections up to the article ASAP.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #560764
Posted Saturday, December 6, 2008 1:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 14, 2014 9:32 AM
Points: 4, Visits: 176
Hello, I am a newbie to sql server and I have created the sp in Master
When 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.sid
UNION 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.sid
UNION 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.sid
UNION 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.sid
UNION 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.sid
UNION 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.sid
UNION 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.sid
UNION 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.sid
UNION 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 34
Incorrect syntax near 'PS'.
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near 'PS'.
Msg 102, Level 15, State 1, Line 53
Incorrect syntax near 'PS'.
Msg 102, Level 15, State 1, Line 56
Incorrect syntax near 'PS'.
Msg 102, Level 15, State 1, Line 72
Incorrect syntax near 'planning'.
Msg 102, Level 15, State 1, Line 75
Incorrect syntax near 'planning'.
Msg 102, Level 15, State 1, Line 173
Incorrect syntax near ')'.
Post #615075
Posted Saturday, December 6, 2008 7:07 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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.
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)



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #615213
Posted Sunday, December 28, 2008 11:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 14, 2014 9:32 AM
Points: 4, Visits: 176
Thanks very much, this is working fine now, really appreciate your answer
Lucien
Post #626310
Posted Sunday, December 28, 2008 1:57 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
Glad I could help.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #626337
Posted Tuesday, June 2, 2009 9:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 4, 2009 11:42 AM
Points: 4, Visits: 31
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.
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 Login = ''' + @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

Post #727547
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse