To find out actual owner of the table

Question

To find out actual owner of the table

srivishiva

Old Hand

Points: 336
More actions
March 24, 2010 at 9:38 am

#218488

Does any body know how we can get the actual owner (username) of the table using a query?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

It sounds like you are thinking every table has an owner the way Oracle works; since SQL 2005, that's not true any more.

now everything is part of a schema, and people have rights to the schema.

some people might have db_owner rights to the schema, but that just means they can do anything to the objects, not that they truely "own" the objects.

you can find who has access to the objects, but there's no true "owner"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

srivishiva Old Hand Points: 336 More actions · Answer 2

OK. Thanks for your explanation. I am in the process of deleting unwanted tables that were created by me so I was trying to find a way to get only those tables.

MSQL9 SSC Journeyman Points: 91 More actions · Answer 3

select o.name as owner from sysobjects o with (nolock) inner join sysusers u with (nolock) on o.uid = u.uid

where u.name = 'username'

Hope this helps.

Mark

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

m_p_weber (3/24/2010)
select o.name as owner from sysobjects o with (nolock) inner join sysusers u with (nolock) on o.uid = u.uid
where u.name = 'username'

Those are both deprecated system objects included only for backward compatability with SQL 2000. They should not be used in new development, partially because they don't show 'new' features like schemas.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

srivishiva Old Hand Points: 336 More actions · Answer 5

The o.name displays the object name.

But even if I choose u.name, it displays the generic owner name like "dbo" or "sys", it does not display the actual owner name like "siva" or something like that

srivishiva Old Hand Points: 336 More actions · Answer 6

Thanks for your answer. I got almost the same answer from Lowell that it is not possible

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

To find out the schema_owner for each table, you could do the following:

Select o.name, schema_name(o.schema_id)

From sys.objects o

To find out further permissions, I have a blog post on the topic at:

http://jasonbrimhall.info/?p=391

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

srivishiva Old Hand Points: 336 More actions · Answer 8

Thanks. This query also returns "dbo", not the NT login or the database userid of the user

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

srivishiva (3/24/2010)
Thanks. This query also returns "dbo", not the NT login or the database userid of the user

You will note that I specified that it will return the schema owner. Since a schema owns the table and not a user. This script is a more preferred script for 2005 due to object deprecation.

To find what users have what permissions to which objects, check the link.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

srivishiva Old Hand Points: 336 More actions · Answer 10

srivishiva

Old Hand

Points: 336

March 24, 2010 at 10:46 am

#1139065

OK. Thanks again

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events