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 [?]; 
,	user_name([uid])
,	[name]
,	sod.[desc]
	[?]..sysobjects  so join #sodesc sod on so.[xtype] = sod.[xtype] collate SQL_Latin1_General_CP1_CI_AS
	user_name([uid]) not in (''sys'', ''INFORMATION_SCHEMA'', ''dbo'')
	and db_name() not in (''tempdb'')
order by
,	[name]
insert into #ownership
exec master..sp_msforeachdb @get_all_object_ownership 

select * from #ownership

Leave a Reply

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

You are commenting using your 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