SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find all objects in Server Owned by a Login


Find all objects in Server Owned by a Login

Author
Message
JC-3113
JC-3113
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1744 Visits: 620
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
tbanks-1094621
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 31
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
JC-3113
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1744 Visits: 620
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
JC-3113
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1744 Visits: 620
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
chumphrey 12211
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 480
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'.
chumphrey 12211
chumphrey 12211
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 480
Sorry, I said I was new. Didn't see the additional pages with the latest version. Thanks. I have no errors.
chumphrey 12211
chumphrey 12211
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 480
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?
Preet_S
Preet_S
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 240
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


Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10180 Visits: 5156
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search