getting user details of a table

Question

getting user details of a table

sathishmangunuri

Grasshopper

Points: 20
More actions
April 12, 2011 at 5:00 am

#97821

Hi ,
I have logged into server with particular user credentials and created some table.Now I need get the details of those tables which are created by this user.
Help me please....
I found principal_id column in both sys.server_principals table and sys.tables,but sys.tables table has null values....
where as sys.server_principals table holds unique value for one user.....
Is there any way to resolve this?
Thanks in advance......

Viewing 5 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

sathishmangunuri (4/12/2011)
Hi ,
I have logged into server with particular user credentials and created some table.Now I need get the details of those tables which are created by this user.

what details specifically do you need? table name and column data are in sys.tables and sys.columns

Help me please....
I found principal_id column in both sys.server_principals table and sys.tables,but sys.tables table has null values....
where as sys.server_principals table holds unique value for one user.....

what values, specifically, are you talking about/ what query did you use? what values are null that you expect to be not null

Is there any way to resolve this?
Thanks in advance......

Remember we are not looking over your shoulder. your question needs to have all the details a new person would need to duplicate on a brand new machine, or follow along to give an informed response.

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!

sathishmangunuri Grasshopper Points: 20 More actions · Answer 2

If i pass the object name ,i should get the user name who created it?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

sathishmangunuri (4/12/2011)
I found principal_id column in both sys.server_principals table and sys.tables,but sys.tables table has null values....
where as sys.server_principals table holds unique value for one user.....

The princpial_id in sys.tables is the owner, not the creator. Any user that's a member of the db_owner role will create tables owned by dbo, resulting in a null principal_id.

If there is a value in there, it links to sys.database_principals, not sys.server_principals

Is there any way to resolve this?

If you need to audit table creation, then you need to either monitor the default trace or use a ddl trigger.

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

chandan_jha18 SSChampion Points: 11330 More actions · Answer 4

sathishmangunuri (4/12/2011)
Hi ,
I have logged into server with particular user credentials and created some table.Now I need get the details of those tables which are created by this user.
Help me please....
I found principal_id column in both sys.server_principals table and sys.tables,but sys.tables table has null values....
where as sys.server_principals table holds unique value for one user.....
Is there any way to resolve this?
Thanks in advance......

you can use the default trace to figure this out or you can go to your database--> right click--> reports--> schema changes history.

Do it soon else these things get overwritten time to time.