SQL 2005 Find Out Who Created a View

Question

Hi:

Does SQL store any information about who originally created a view, or who last modified it?

Answer 1

I am not sure if there is a way to see who created the view but sp_help will get you some information on when it was created

sp_help viewMyView

sp_help works on any and all database objects BTW.

Answer 2

It's too late now, but if you were using 2008 you could create an audit that will track future changes.

EDIT: found it!

    select p.name, v.* 
from sys.all_views v, sys.database_principals p, sys.schemas s
where p.principal_id = s.principal_id
and v.schema_id = s.schema_id
and v.name = 'your_view_name'

This will produce a number of interesting details about the views in your database, including the column principal_id. Join with sys.database_principals on principal_id for the username!

Answer 3

SQL Server does not store explicit information about who created or modified an object. There is information in the metadata catalog about who is the owner of a given object, or to what schema does the object belong to:

select * from sys.objects where object_id = object_id('<object name>');

Depending on the object type either the principal_id is populated with the database principal ID of the owner, or the schema_id is populated with the Id of the schema to which the object belongs. All schemas have an owner and which can be retrieved from the metadata catalog:

select * from sys.schemas

However note that these will only reveal the owner of the object. The owner does not necessarily means the user that created it or modified it. Ownership of objects can be changed during creation or after creation with the ALTER AUTHORIZATION statement, making identification by ownership unreliable at best. Also all members of sysadmin role map to the same database principal, dbo, in every database.

To properly identify the the user that created an object you should deploy auditing methods, but that require prior deployment of the audit. Some forensics can be done after the fact if audit was not deployed:

• You can dig into the log file, Paul Randal has an example in his recent blog.
• You can look into the default trace