December 3, 2009 at 2:10 pm
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
December 3, 2009 at 11:39 pm
JC-3113 (12/3/2009)
Hi RBarry Youngsorry 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...
[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 4, 2009 at 7:03 am
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!
December 4, 2009 at 10:29 am
Hi RBarry Young
are yiou sure
it is still not working
i copied the code from above
Thanks
Jim
December 4, 2009 at 10:35 am
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 4, 2009 at 11:03 am
Sorry
Msg 207, Level 16, State 1, Server NSAB-SS83-SQL-N, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Server NSAB-SS83-SQL-N, Line 7
Invalid column name 'Login'.
Jim
December 4, 2009 at 11:30 am
Right you are. It works when you don't specify a login, but to specify a login, you'll need this:
Alter Proc spLogin_OwnedObjects ( @login as SYSNAME ) As
/*
Display all objects in all DBs owned by the Login.
2008-07-06 RBarryYoung Created.
2008-08-28 RBarryYoung Corrected of Windows vs SS default collations.
2008-12-06 RBarryYoung Fixed for spaces in DB names.
2009-06-01 TBanks Fixed for large quantities of DBs to avoid the 256-table union limit
Test:
spLogin_OwnedObjects 'sa'
*/
set nocount on
create table ##objectowners(
[DBID] int,
DBName varchar(255),
[Login] varchar(255),
[User] varchar(255),
name varchar(255),
[object_id] [int],
[principal_id] [int],
[schema_id] [int],
[parent_object_id] [int],
[type] [char](2),
[type_desc] [nvarchar](60),
[create_date] [datetime],
[modify_date] [datetime],
[is_ms_shipped] [bit],
[is_published] [bit],
[is_schema_published] [bit])
declare @sql varchar(MAX),
@DB_Objects varchar(512)
Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User], O.*
From [%D%].sys.objects o
Join [%D%].sys.database_principals u
ON Coalesce(o.principal_id, (Select S.Principal_ID from [%D%].sys.schemas S Where S.Schema_ID = O.schema_id))
= U.principal_id
left join [%D%].sys.server_principals L on L.sid = u.sid
'
Select @sql = 'insert ##objectowners SELECT * FROM
(Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '
+ Replace(@DB_objects, '%D%', [name])
From master.sys.databases
Where [name] = 'master'
select @sql = @sql + case when @login is null then ') oo'
else ') oo Where Login = ''' + @login + ''''
end
EXEC (@sql)
declare @db varchar(100),
@db_id int
DECLARE db_cursor CURSOR FOR
SELECT name, database_id
FROM master.sys.databases
where name <> 'master'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db, @db_id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'insert ##objectowners Select '+Cast(@db_id as varchar(9))+', '''+@db+''', '
+ Replace(@DB_objects, '%D%', @db)
select @sql = @sql + case when @login is null then ' '
else ' Where L.name = ''' + @login + ''''
end
exec (@sql)
FETCH NEXT FROM db_cursor INTO @db, @db_id
END
CLOSE db_cursor
DEALLOCATE db_cursor
select * from ##objectowners
drop table ##objectowners
set nocount off
December 4, 2009 at 11:41 am
Well...
it is geting there
at least no errors
but i have names like this: LOSANGELES-2K\cox.jim.adm
EXEC 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 limit
any ideas ?
Thaks
Jim
April 21, 2010 at 11:37 am
Anybody
do we have a correct, working version of this script
ther have been hanges, and i cannot get it to work
it would be nice if we had a working copy some where please
Thanks
Jim
August 26, 2010 at 1:42 pm
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 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Login'.
August 26, 2010 at 1:50 pm
Sorry, I said I was new. Didn't see the additional pages with the latest version. Thanks. I have no errors.
August 26, 2010 at 2:44 pm
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?
September 16, 2011 at 10:58 am
tbanks-1094621 (12/4/2009)
Right you are. It works when you don't specify a login, but to specify a login, you'll need this:
Alter Proc spLogin_OwnedObjects ( @login as SYSNAME ) As
/*
Display all objects in all DBs owned by the Login.
2008-07-06 RBarryYoung Created.
2008-08-28 RBarryYoung Corrected of Windows vs SS default collations.
2008-12-06 RBarryYoung Fixed for spaces in DB names.
2009-06-01 TBanks Fixed for large quantities of DBs to avoid the 256-table union limit
Test:
spLogin_OwnedObjects 'sa'
*/
set nocount on
create table ##objectowners(
[DBID] int,
DBName varchar(255),
[Login] varchar(255),
[User] varchar(255),
name varchar(255),
[object_id] [int],
[principal_id] [int],
[schema_id] [int],
[parent_object_id] [int],
[type] [char](2),
[type_desc] [nvarchar](60),
[create_date] [datetime],
[modify_date] [datetime],
[is_ms_shipped] [bit],
[is_published] [bit],
[is_schema_published] [bit])
declare @sql varchar(MAX),
@DB_Objects varchar(512)
Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User], O.*
From [%D%].sys.objects o
Join [%D%].sys.database_principals u
ON Coalesce(o.principal_id, (Select S.Principal_ID from [%D%].sys.schemas S Where S.Schema_ID = O.schema_id))
= U.principal_id
left join [%D%].sys.server_principals L on L.sid = u.sid
'
Select @sql = 'insert ##objectowners SELECT * FROM
(Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '
+ Replace(@DB_objects, '%D%', [name])
From master.sys.databases
Where [name] = 'master'
select @sql = @sql + case when @login is null then ') oo'
else ') oo Where Login = ''' + @login + ''''
end
EXEC (@sql)
declare @db varchar(100),
@db_id int
DECLARE db_cursor CURSOR FOR
SELECT name, database_id
FROM master.sys.databases
where name <> 'master'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db, @db_id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'insert ##objectowners Select '+Cast(@db_id as varchar(9))+', '''+@db+''', '
+ Replace(@DB_objects, '%D%', @db)
select @sql = @sql + case when @login is null then ' '
else ' Where L.name = ''' + @login + ''''
end
exec (@sql)
FETCH NEXT FROM db_cursor INTO @db, @db_id
END
CLOSE db_cursor
DEALLOCATE db_cursor
select * from ##objectowners
drop table ##objectowners
set nocount off
@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) ;
create table #objectowners(
[DBID] int,
DBName varchar(255),
[Login] varchar(255),
[User] varchar(255),
name varchar(255),
[object_id] [int],
[principal_id] [int],
[schema_id] [int],
[parent_object_id] [int],
[type] [char](2),
[type_desc] [nvarchar](60),
[create_date] [datetime],
[modify_date] [datetime],
[is_ms_shipped] [bit],
[is_published] [bit],
[is_schema_published] [bit])
declare @sql varchar(MAX),
@DB_Objects varchar(512),
@login sysname
set @login = 'SomeName'
Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User], O.*
From [%D%].sys.objects o
Join [%D%].sys.database_principals u
ON Coalesce(o.principal_id, (Select S.Principal_ID from [%D%].sys.schemas S Where S.Schema_ID = O.schema_id))
= U.principal_id
left join [%D%].sys.server_principals L on L.sid = u.sid
'
set @sql = ''
Select @sql = @sql + 'insert #objectowners SELECT * FROM
(Select '+Cast(database_id as varchar(9))+' as DBID,''' + [Name] + ''' as DBName, '
+ Replace(@DB_objects, '%D%', [name]) + case when @login is null then ') oo ;'
else ') oo Where Login like ''%' + @login + '%''' end + ';'
From master.sys.databases
EXEC (@sql)
select * from #objectowners
drop table #objectowners
set nocount off
November 22, 2013 at 3:01 am
Hi RBarryYoung,
Your script is useful. I tested it and it failed on an offline database. You can update the code so that only online databases are taken in consideration.
Thanks and regards
IgorMi
Igor Micev,
My blog: www.igormicev.com
September 15, 2020 at 3:23 pm
just add the following bold line.
WHERE [NAME] != 'master'
AND state_desc = 'ONLINE'
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply