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: 8366 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

tbanks-1094621

SSC Rookie

Points: 48

December 4, 2009 at 7:03 am

#1087677

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: 8366 More actions · Answer 4

JC-3113

SSCrazy Eights

Points: 8366

December 4, 2009 at 10:29 am

#1087889

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: 8366 More actions · Answer 6

JC-3113

SSCrazy Eights

Points: 8366

December 4, 2009 at 11:03 am

#1087907

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: 8366 More actions · Answer 8

JC-3113

SSCrazy Eights

Points: 8366

December 4, 2009 at 11:41 am

#1087927

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: 8366 More actions · Answer 9

JC-3113

SSCrazy Eights

Points: 8366

April 21, 2010 at 11:37 am

#1155554

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?