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 Friday, December 4, 2009 11:03 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
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
Post #829117
Posted Friday, December 4, 2009 11:30 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
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


Post #829132
Posted Friday, December 4, 2009 11:41 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
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
Post #829140
Posted Wednesday, April 21, 2010 11:37 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
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
Post #907977
Posted Thursday, August 26, 2010 1:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:00 PM
Points: 37, Visits: 459
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'.
Post #975916
Posted Thursday, August 26, 2010 1:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:00 PM
Points: 37, Visits: 459
Sorry, I said I was new. Didn't see the additional pages with the latest version. Thanks. I have no errors.
Post #975918
Posted Thursday, August 26, 2010 2:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:00 PM
Points: 37, Visits: 459
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?
Post #975950
Posted Friday, September 16, 2011 10:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:45 AM
Points: 236, Visits: 239
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

Post #1176546
Posted Friday, November 22, 2013 3:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 2,962, Visits: 2,979
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,
SQL Server developer at Seavus
www.seavus.com
Post #1516698
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse