Re: How do determine who is the owner of an object?

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@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:3624EDF3-0531-462E-B641-40124E28B6F5@xxxxxxxxxxxxxxxx
>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@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxx> wrote in message news:OSB7k%23sHHHA.420@xxxxxxxxxxxxxxxxxxxxxxx
Walter
You cannot drop Logins that have owned objects





"WalterWalt" <,> wrote in message news:OvVYN3sHHHA.4688@xxxxxxxxxxxxxxxxxxxxxxx
I want to find objects that are owned by SQL Logins that no longer exist.
Help is appreciated,
Thanks









.