Agent Jobs

Find Object Ownership Across All Databases

Whenever you’re clearing out your database servers and removing disabled, or expired accounts; you’ll need to check to see if there is any standing object ownership on some of those accounts. This way you can transfer ownership before removing the old accounts.
Here is some SQL logic to show all the object ownership across all databases. This excludes the existing defaults such as ‘dbo’, ‘sys’, or ‘information_schema’ owners.

use master;
set nocount on

if object_id('tempdb..#sodesc') is not null drop table	#sodesc
create table	#sodesc ([xtype] varchar(3), [desc] varchar(255))
insert into	#sodesc select substring([name], 0, charindex(':', [name], 0)),	right([name], charindex(':', reverse([name])) - 1) 
from master..[spt_values] where [name] like '%:%'

if object_id('tempdb..#ownership') is not null drop table #ownership
create table	#ownership ([database] varchar(255), [user_name] varchar(255), [object_name] varchar(255), [object_type] varchar(255))

declare @get_all_object_ownership	varchar(max)
set	@get_all_object_ownership	=
'
use [?]; 
select
	db_name()
,	user_name([uid])
,	[name]
,	sod.[desc]
from
	[?]..sysobjects  so join #sodesc sod on so.[xtype] = sod.[xtype] collate SQL_Latin1_General_CP1_CI_AS
where
	user_name([uid]) not in (''sys'', ''INFORMATION_SCHEMA'', ''dbo'')
	and db_name() not in (''tempdb'')
order by
	user_name([uid])
,	[name]
'
insert into #ownership
exec master..sp_msforeachdb @get_all_object_ownership 

select * from #ownership
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s