Get Objects NT Owner Name, Not dbo

[mkelly11]

Is there any way, via systems tables or other method, to capture the NT or SQL User name when the user is listed as sysadmin? For example I'd like to know which user is the owner of an object and dbo, for obvious reasons, does not supply the detail I'm looking for.

I was hoping to find this information stored in the system tables, however all I find is the user names associated with the db and all objects owned by dbo...very frustrating!

I'd like to create a metadata table within each db, that captures each object created and NT or SQL username who created the object along with dates and other important info (documentation is not something we are good at keeping up with). The whole process would be automated, so developers don't have to worry documenting who did what.

Any help is much appreciated!!

 

[SQLSister]

SYSTEM_USER() will give the system user that you can use in a stored procedure to gather information. I don't believe that SQL Server stores the exact name of who created a table or procedure if that person has system or database ownership rights because the owner is dbo.

You could create templates in Query analyzer to sue for create table, alter table, etc. that run a process to store this information in a table you create, then require the developers to use the template whenever they create an object. But this won't prevent them from "forgetting" to use it.


Questions about posting. See faq183-874

 

[mrdenny]

I agree with SQLSister here. When a person with sysadmin permissions creates an object they have an inheriated permission into the database as dbo, so when the object is created it's created with the owner being dbo.

Also when you create an object and don't specify the owner name (which is what dbo really is in the dbo.tablename syntax) it will default to dbo if you have sysadmin rights on the server. If you don't, and you aren't listed as the dbo it will default to your account name within the database.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLBill]

Remember, giving someone sysadmin rights is basically saying "I don't care what you do on the system, I trust you to not mess anything up". That's why you really can't track what a sysadmin does. It's a catch-22 type of thing. If you can track a sysadmin's actions, they have the ability (rights/permissions) to delete or change that tracking - so what's the use?

-SQLBill

 

[mkelly11]

Thanks for the quick replies, I'll stick to enforcing the fact that all object creations must be manually noted in a "meta data" table. Unfortunately we are all sysadmins by title, so I can't demote people to lower level permissions simply for tracking purposes.

Thanks.

 

[SQLSister]

You really don't want people creating objects as anything but dbo anyway. That can create some real permissions nightmares.

Questions about posting. See faq183-874