DBO ownership

Is there a quick query of process that can be used to find any objects in a
database that are NOT owned by DBO ? What and how' Thank you..What version of SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:e4WsK8y5GHA.4116@.TK2MSFTNGP03.phx.gbl...
> Is there a quick query of process that can be used to find any objects in
a database that are NOT
> owned by DBO ? What and how' Thank you..
>|||the databases are 2000 sp4, But I also have a 2005 server setup for training
and testing, that I sometimes use to connect to my 2000 databases.
So if you could let me how it could be done from both the 2000 and the 2005
servers for a 2000 database, I would appreciate that. Thank you!!
========================================
=====================
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eruxLEz5GHA.3436@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:e4WsK8y5GHA.4116@.TK2MSFTNGP03.phx.gbl...
>|||For SQL 2000
select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
from sysobjects where type = 'U' and user_name(uid) !='DBO'
order by uid, name
For SQL 2005
The above query will work out good for SQL 2005 as well.
select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
from sysobjects where type = 'U' and user_name(uid) !='DBO'
order by uid, name
Thanks
Hari
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:elwt$Sz5GHA.4116@.TK2MSFTNGP03.phx.gbl...
> the databases are 2000 sp4, But I also have a 2005 server setup for
> training and testing, that I sometimes use to connect to my 2000
> databases.
> So if you could let me how it could be done from both the 2000 and the
> 2005 servers for a 2000 database, I would appreciate that. Thank you!!
> ========================================
=====================
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:eruxLEz5GHA.3436@.TK2MSFTNGP03.phx.gbl...
>|||I take it that this would be run against both MSDB and MASTER. But when I
run
use master
select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
from sysobjects
--where user_name(uid) !='DBO'
order by uid, name
Just to see what is out there, I don't see tables that I thought I would.
Is there someplace else that I should run this?
===============================
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23pNFzp15GHA.512@.TK2MSFTNGP06.phx.gbl...
> For SQL 2000
> select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
> from sysobjects where type = 'U' and user_name(uid) !='DBO'
> order by uid, name
>
> For SQL 2005
> The above query will work out good for SQL 2005 as well.
> select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
> from sysobjects where type = 'U' and user_name(uid) !='DBO'
> order by uid, name
>
> Thanks
> Hari
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:elwt$Sz5GHA.4116@.TK2MSFTNGP03.phx.gbl...
>|||Are you certain that you have tables in master owned by someone else but dbo
?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:%236cDMw75GHA.3836@.TK2MSFTNGP06.phx.gbl...
>I take it that this would be run against both MSDB and MASTER. But when I
run
> use master
> select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
> from sysobjects
> --where user_name(uid) !='DBO'
> order by uid, name
> Just to see what is out there, I don't see tables that I thought I would.
Is there someplace else
> that I should run this?
> ===============================
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23pNFzp15GHA.512@.TK2MSFTNGP06.phx.gbl...
>|||No. everything that I saw was owned by DBO, (I had commented that part out)
but I did not notice tables in the list of 1213 objects. That could be that
I did not look carefully enough, there were over 1300 records... My
question was, is the Master the one central location where I would find
everything that might not be owned by DBO or do I need to look in multiple
places? You see there are some analysts here that use different
procedures and to create import DBs and I am just looking to be prepared.
Eventually I may create an alert or a job that runs daily that would alert
me in the event that such an object was created. It has happened twice
where a table was created with the login name as the owner, and I get the
complaints that something didn't work because of access that I have
restricted....
===================================
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O$8Eww85GHA.3564@.TK2MSFTNGP02.phx.gbl...
> Are you certain that you have tables in master owned by someone else but
> dbo?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:%236cDMw75GHA.3836@.TK2MSFTNGP06.phx.gbl...
>|||You would have to look in each database. Google for sp_msforeachdb, and you
will find how to do
that. Note that the proc is undocumented, so use at own risk.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eYs0KW95GHA.2288@.TK2MSFTNGP05.phx.gbl...
> No. everything that I saw was owned by DBO, (I had commented that part out
) but I did not notice
> tables in the list of 1213 objects. That could be that I did not look car
efully enough, there
> were over 1300 records... My question was, is the Master the one central
location where I would
> find everything that might not be owned by DBO or do I need to look in mul
tiple places? You
> see there are some analysts here that use different procedures and to crea
te import DBs and I am
> just looking to be prepared. Eventually I may create an alert or a job tha
t runs daily that would
> alert me in the event that such an object was created. It has happened tw
ice where a table was
> created with the login name as the owner, and I get the complaints that so
mething didn't work
> because of access that I have restricted....
> ===================================
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:O$8Eww85GHA.3564@.TK2MSFTNGP02.phx.gbl...
>