how to locate the exact user name who owns a db object

hi all,
In sql 2000, how can you find out the exact user name who owns/created a db
object? They are generally recorded as 'dbo' in the sysobjects table, how
can we find out the specific user login name behind the 'dbo' entry?
many thanks,
JJThe 'dbo' user in a database is a special user that maps to the login
who owns the database (usually the login who initially created the
database). The system stored proc "exec sp_helpdb '<dbname>'" will tell
you who the owner of the database is and that login will be the one that
maps to the dbo user in the database.
Other, less Microsoft approved, ways of finding this info would be:
* "select * from master.dbo.sysdatabases" (the sid column is the
login that owns a given database, ie. that maps to the dbo user in
that database, and you can join that to master.dbo.syslogins to
get more info about that login)
* "select * from <dbname>.dbo.sysusers" (the dbo user in the
database is always uid 1; the sid column in that table will map
back to the master.dbo.syslogins table to tell you who owns the
database...unless the database user is an orphaned user (the sid
doesn't map back to any row in master.dbo.syslogins) which often
happens when you restore DBs from other servers because the other
server has different data in its master.dbo.syslogins table; this
can be corrected with sp_change_users_login)
* You could use the SUSER_SNAME() function with the sysusers table
like this:
select SUSER_SNAME(sid) from <dbname>.dbo.sysusers where uid = 1
Bear in mind, not every object in a DB has to be owned by the dbo user,
although this is quite normal. To find out which DB user owns a
specific object in the database you can use the OBJECTPROPERTY()
function like this:
select USER_NAME(OBJECTPROPERTY(OBJECT_ID('MyTa
ble'),'OwnerId'))
However, sp_helpdb is probably the easiest. Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
JJ Wang wrote:

>hi all,
>In sql 2000, how can you find out the exact user name who owns/created a db
>object? They are generally recorded as 'dbo' in the sysobjects table, how
>can we find out the specific user login name behind the 'dbo' entry?
>many thanks,
>JJ
>|||JJ,
I think that would need to be accomplished by using a source control system
like Visual SourceSafe as many logins may have the ability to have dbo be
the owner of an object.
HTH
Jerry
"JJ Wang" <JJWang@.discussions.microsoft.com> wrote in message
news:73FC551C-3600-4E8F-8B37-B2BB9E890879@.microsoft.com...
> hi all,
> In sql 2000, how can you find out the exact user name who owns/created a
> db
> object? They are generally recorded as 'dbo' in the sysobjects table, how
> can we find out the specific user login name behind the 'dbo' entry?
> many thanks,
> JJ|||Hi,
the only wat i think for that is too tell your developers/DBAs to use full
name while creating db / objects .
Regards