July 7, 2008 at 11:08 pm
Comments posted to this topic are about the item Find all objects in Server Owned by a Login
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 10:58 am
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.
August 28, 2008 at 11:21 am
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-06RBarryYoungCreated.
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'
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).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 11:49 am
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 --
August 28, 2008 at 1:14 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 6, 2008 at 1:03 am
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 ')'.
December 6, 2008 at 7:07 pm
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-06RBarryYoungCreated.
2008-08-28RBarryYoungCorrected of Windows vs SS default collations.
2008-12-06RBarryYoungFixed 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'
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)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 28, 2008 at 11:31 am
Thanks very much, this is working fine now, really appreciate your answer
Lucien
December 28, 2008 at 1:57 pm
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 2, 2009 at 9:57 am
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
December 3, 2009 at 1:04 pm
Hi RBarryYoung
still having issues:
E:\SQL_Server_SQL>sqlcmd
1> --http://www.sqlservercentral.com/scripts/Administration/63631/
2> --Find all objects in Server Owned by a Login
3> --By RBarry Young, 2008/08/28
4>
5>
6> CREATE Proc spLogin_OwnedObjects ( @login as SYSNAME ) As
7> /*
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 Collations
12~ (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_id
21~ , o.principal_id
22~ , o.schema_id
23~ , o.parent_object_id
24~ , o.type COLLATE DATABASE_DEFAULT as [type]
25~ , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]
26~ , o.create_date
27~ , o.modify_date
28~ , o.is_ms_shipped
29~ , o.is_published
30~ , o.is_schema_published
31~ From %D%.sys.objects o
32~ Join %D%.sys.database_principals u
33~ ON Coalesce(o.principal_id
34~ , (Select S.Principal_ID from %D%.sys.schemas S Where S.Schema_ID = O.schema_id))
35~ = U.principal_id
36~ Left Join %D%.sys.server_principals L on L.sid = u.sid
37~ '
38>
39> Select @sql = 'SELECT * FROM
40~ (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '
41> + Replace(@DB_objects, '%D%', [name])
42> From master.sys.databases
43> 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.databases
48> Where [name] != 'master'
49>
50> Select @sql = @sql + ') oo Where Login = ''' + @login + ''''
51>
52> print @sql
53> EXEC (@sql)
54> go
1>
2> EXEC spLogin_OwnedObjects 'sa'
3> go
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 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.sid
UNION 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.sid
UNION 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.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 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.sid
UNION 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.sid
UNION 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.sid
UNION 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.sid
UNION 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.o
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 262
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 265
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 281
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 284
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 338
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 341
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 357
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 360
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 376
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 379
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 724
Incorrect syntax near ')'.
1>
December 3, 2009 at 1:29 pm
Hmmm, weird. It's obvious what's wrong, but I though that I had fixed it long ago. Well, this should fix it anyway:
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)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 3, 2009 at 1:34 pm
Excellent RBarryYoung
it was not obvious to me apparently.. what was it ?
Thanks
Jim
December 3, 2009 at 1:42 pm
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...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 3, 2009 at 2:00 pm
Well at least it was obvious to you and you fixed it quickly
thanks again
jim
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply