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 Thursday, December 3, 2009 1:04 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, Visits: 620
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>
Post #828462
Posted Thursday, December 3, 2009 1:29 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 24, 2014 11:52 AM
Points: 9,294, Visits: 9,484
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)



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #828480
Posted Thursday, December 3, 2009 1:34 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, Visits: 620
Excellent RBarryYoung

it was not obvious to me apparently.. what was it ?

Thanks
Jim
Post #828484
Posted Thursday, December 3, 2009 1:42 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 24, 2014 11:52 AM
Points: 9,294, Visits: 9,484
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...


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #828487
Posted Thursday, December 3, 2009 2:00 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, Visits: 620
Well at least it was obvious to you and you fixed it quickly

thanks again
jim
Post #828495
Posted Thursday, December 3, 2009 2:10 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, Visits: 620
Hi RBarry Young

sorry to bug you again, but i came across another issue when running this on another server
seems there's a 256 limit somewhere

Msg 106, Level 15, State 1, Server NSAB-SS83-SQL-N, Line 1218
Too many table names in the query. The maximum allowable is 256.
Msg 102, Level 15, State 1, Server NSAB-SS83-SQL-N, Line 1234
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Server NSAB-SS83-SQL-N, Line 1256
Incorrect syntax near ')'.

Thanks
Jiim
Post #828502
Posted Thursday, December 3, 2009 11:39 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 24, 2014 11:52 AM
Points: 9,294, Visits: 9,484
JC-3113 (12/3/2009)
Hi RBarry Young

sorry to bug you again, but i came across another issue when running this on another server
seems there's a 256 limit somewhere

Msg 106, Level 15, State 1, Server NSAB-SS83-SQL-N, Line 1218
Too many table names in the query. The maximum allowable is 256.
Msg 102, Level 15, State 1, Server NSAB-SS83-SQL-N, Line 1234
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Server NSAB-SS83-SQL-N, Line 1256
Incorrect syntax near ')'.

Thanks
Jiim

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


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #828652
Posted Friday, December 4, 2009 7:03 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
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!
Post #828867
Posted Friday, December 4, 2009 10:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, Visits: 620
Hi RBarry Young

are yiou sure

it is still not working

i copied the code from above

Thanks
Jim
Post #829099
Posted Friday, December 4, 2009 10:35 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
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 #829104
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse