|
Home > Archive > MS SQL Server security > December 2006 > How do determine who is the owner of an object?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How do determine who is the owner of an object?
|
|
| WalterWalt 2006-12-13, 7:12 pm |
| I want to find objects that are owned by SQL Logins that no longer exist.
Help is appreciated,
Thanks
| |
| Uri Dimant 2006-12-13, 7:12 pm |
| Walter
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
>
| |
| WalterWalt 2006-12-13, 7:12 pm |
| I dropped the builtin\administrato
rs 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...
>
>
| |
| rja.carnegie@excite.com 2006-12-13, 7:12 pm |
| 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
| |
| Dan Guzman 2006-12-14, 12:12 am |
| >I dropped the builtin\administrato
rs 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\administrat
ors 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\administrato
rs 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...
>
>
|
|
|
|
|