Find all objects in Server Owned by a Login

Question

Post reply

Find all objects in Server Owned by a Login

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 1

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

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

[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]

tbanks-1094621 SSC Rookie Points: 48 More actions · Answer 3

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!

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 4

Hi RBarry Young

are yiou sure

it is still not working

i copied the code from above

Thanks

Jim

tbanks-1094621 SSC Rookie Points: 48 More actions · Answer 5

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

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 6

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

tbanks-1094621 SSC Rookie Points: 48 More actions · Answer 7

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

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 8

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

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 9

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

chumphrey 12211 Ten Centuries Points: 1223 More actions · Answer 10

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?