How do determine who is the owner of an object?

I want to find objects that are owned by SQL Logins that no longer exist.
Help is appreciated,
ThanksWalter
You cannot drop Logins that have owned objects
"WalterWalt" <,> wrote in message
news:OvVYN3sHHHA.4688@.TK2MSFTNGP04.phx.gbl...
>I want to find objects that are owned by SQL Logins that no longer exist.
> Help is appreciated,
> Thanks
>|||I dropped the builtin\administrators login and have a sneaking suspicion
that that left some objects with an orphaned owner. I would like to confirm
this is not the case by running a query to get the owner of all obects. Can
you answer my question? Thanks.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OSB7k%23sHHHA.420@.TK2MSFTNGP06.phx.gbl...
> Walter
> You cannot drop Logins that have owned objects
>
>
> "WalterWalt" <,> wrote in message
> news:OvVYN3sHHHA.4688@.TK2MSFTNGP04.phx.gbl...
>|||WalterWalt wrote:
> I want to find objects that are owned by SQL Logins that no longer exist.
> Help is appreciated,
> Thanks
I think sysobjects.uid is the user ID in sysusers, in a database.
Linking database users to logins is a further step.
For instance:
create table tempdb.guest.guesttable ( i int)
select * from tempdb.dbo.sysobjects where name = 'guesttable'
select * from tempdb.dbo.sysusers order by uid
HTH|||>I dropped the builtin\administrators login and have a sneaking suspicion
>that that left some objects with an orphaned owner.
Why do you suspect this? Assuming SQL 2000, logins do not own database
objects directly. Database users own database objects and SQL will not let
you drop a database user that owns objects. Also, objects cannot be owned
by Windows groups (builtin\administrators is a Windows group).
Separately, users are mapped to database logins. SQL Server will not let
you drop a login that is mapped to a database user. However, you can end up
with orphaned users (users without logins) if you restore/attach a database.

> I would like to confirm this is not the case by running a query to get the
> owner of all obects. Can you answer my question?
Run the query below in your databases. I would expect that ObjectOwner will
not be NULL. OwnerLogin may be null for system schema or if you have
orphaned database users that also own objects.
SELECT DISTINCT
u.name AS ObjectOwner,
l.name AS OwnerLogin
FROM sysobjects o
LEFT JOIN sysusers u ON
u.uid = o.uid
LEFT JOIN master.dbo.syslogins l ON
l.sid = u.sid
Hope this helps.
Dan Guzman
SQL Server MVP
"WalterWalt" <,> wrote in message
news:egKnrGtHHHA.1816@.TK2MSFTNGP06.phx.gbl...
>I dropped the builtin\administrators login and have a sneaking suspicion
>that that left some objects with an orphaned owner. I would like to
>confirm this is not the case by running a query to get the owner of all
>obects. Can you answer my question? Thanks.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OSB7k%23sHHHA.420@.TK2MSFTNGP06.phx.gbl...
>|||OwnerLogin for sys is null. Does that mean there are orphaned database
users?
Thanks for you help.
If I logon to my local machine that is running SQL Server 2000 as
administrator, and then logon to SQL Server using Windows Authentication I
am a member of the sysadmin server role even though I don't have a SQL
Server login explicitly created for Administrator. I get my permissions
implicitly through the BUILTIN\Administrators group. While logged into SQL
Server in this context I create a database called TEST and a table called
tblTEST. Then I log out of SQL and then back in as sa. I delete the
BUILTIN\Administrators group. So who is the owner of db TEST and tblTest
and how do I query for other objects like that?
Thanks and sorry to Uri is sounded snappy.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:3624EDF3-0531-462E-B641-40124E28B6F5@.microsoft.com...
> Why do you suspect this? Assuming SQL 2000, logins do not own database
> objects directly. Database users own database objects and SQL will not
> let you drop a database user that owns objects. Also, objects cannot be
> owned by Windows groups (builtin\administrators is a Windows group).
> Separately, users are mapped to database logins. SQL Server will not let
> you drop a login that is mapped to a database user. However, you can end
> up with orphaned users (users without logins) if you restore/attach a
> database.
>
> Run the query below in your databases. I would expect that ObjectOwner
> will not be NULL. OwnerLogin may be null for system schema or if you have
> orphaned database users that also own objects.
> SELECT DISTINCT
> u.name AS ObjectOwner,
> l.name AS OwnerLogin
> FROM sysobjects o
> LEFT JOIN sysusers u ON
> u.uid = o.uid
> LEFT JOIN master.dbo.syslogins l ON
> l.sid = u.sid
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "WalterWalt" <,> wrote in message
> news:egKnrGtHHHA.1816@.TK2MSFTNGP06.phx.gbl...
>|||> OwnerLogin for sys is null. Does that mean there are orphaned database
> users?
System schema users (e.g. INFORMATION_SCHEMA) are special cases and don't
need to be associated with logins in SQL 2000. However, ths sys schema was
introduced in SQL 2005 - aren't we were talking about SQL 2000 here? There
are major differences between SQL 2000 and 2005 related to user/schema
separation.

> I delete the BUILTIN\Administrators group. So who is the owner of db TEST
> and tblTest
The tblTest table will continue to be owned by user dbo. The TEST database
will still to be owned by your Windows account. Note that the database
owner database owner will be your Windows account because you connected via
Windows group membership (BUILTIN\Administrators) when you created the
database. Databases can't be owned by Windows groups.

> and how do I query for other objects like that?
Database ownership is recorded in sysdatabases and also as the mapping
between the dbo database user and syslogins. The login mapped to the dbo
user and the database owner should be the same but can get out-of-sync after
a restore or attach. You can use sp_changedbowner to change/fix database
ownership. The query below can identify a mismatch.
SELECT
u.name AS DatabaseUser,
l.name AS DboLogin,
l2.name AS DatabaseOwner
FROM sysusers u
LEFT JOIN master.dbo.syslogins l ON
l.sid = u.sid
JOIN master.dbo.sysdatabases d on
d.name = DB_NAME()
LEFT JOIN master.dbo.syslogins l2 ON
l2.sid = d.sid
WHERE u.name = 'dbo'
Hope this helps.
Dan Guzman
SQL Server MVP
"WalterWalt" <,> wrote in message
news:Ow6jV%236HHHA.4992@.TK2MSFTNGP04.phx.gbl...
> OwnerLogin for sys is null. Does that mean there are orphaned database
> users?
> Thanks for you help.
> If I logon to my local machine that is running SQL Server 2000 as
> administrator, and then logon to SQL Server using Windows Authentication I
> am a member of the sysadmin server role even though I don't have a SQL
> Server login explicitly created for Administrator. I get my permissions
> implicitly through the BUILTIN\Administrators group. While logged into
> SQL Server in this context I create a database called TEST and a table
> called tblTEST. Then I log out of SQL and then back in as sa. I delete
> the BUILTIN\Administrators group. So who is the owner of db TEST and
> tblTest and how do I query for other objects like that?
> Thanks and sorry to Uri is sounded snappy.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:3624EDF3-0531-462E-B641-40124E28B6F5@.microsoft.com...
>