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 04, 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 04, 2009 11:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 04, 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 04, 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 @ 8:54 AM
Points: 35, Visits: 431
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 @ 8:54 AM
Points: 35, Visits: 431
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 @ 8:54 AM
Points: 35, Visits: 431
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:37 AM
Points: 2,725, Visits: 2,634
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
Post #1516698
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse