July 25, 2005 at 5:39 pm
Is there a way to find out who (which login) created an object in SQL Server?
The owner is dbo, but I would like to find out who actually created this object.
Any ideas?
July 26, 2005 at 1:29 am
I might be wrong, but I am pretty certain that there is no way to check which login created an object. In fact, the login might not even exist anymore.
July 26, 2005 at 6:33 am
I can't think of a way either, but it does give you a damn good reason not to allow users to create objects without a proper change control procedure.
July 27, 2005 at 11:16 am
After the fact you're out of like. Prior to, you can set up appropriate traces using Profiler or the trace stored procedures. You can key on object creation/deletion as they are events. However, if you want to track ALTER events you're going to have to parse T-SQL queries as there aren't separate events for ALTER commands.
K. Brian Kelley
@kbriankelley
July 27, 2005 at 12:52 pm
If I recall correctly, 2005 allows you to audit changes to the system catalog, so it would be possible to log the creation of database objects. But under 2000, as far as I know, you're out of luck.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 27, 2005 at 1:15 pm
You can with 2000 if you use tracing. That's the audit mechanism. There are trace events corresponding to object creation/deletion.
K. Brian Kelley
@kbriankelley
July 27, 2005 at 1:28 pm
Brian: You can with 2000 if you use tracing. That's the audit mechanism. There are trace events corresponding to object creation/deletion.
But then you're dealing with having Profiler running constantly, how much of a performance hit on the server is that? I normally only ran Profiler when I was trying to fix a specific problem, I never ran it full time.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 27, 2005 at 2:23 pm
You don't have to have profiler running constantly. You do have to have traces running. And yes, there is some performance hit. However, you can specify to only run against the events in question. The amount of information you're tracing determines the performance hit. But if you're restrictive on your events and data columns, this is far less intrusive than the standard Profiler templates.
K. Brian Kelley
@kbriankelley
July 27, 2005 at 4:15 pm
Thanks, Brian! I didn't know that you could have traces running outside of Profiler, I just did some reading up on it in BOL. I would assume that this is less of a performance hit than having it running through Profiler?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 28, 2005 at 3:04 am
Correct, in SQL Server 2005 it is possible to create DDL Triggers, i.e. triggers that are triggered by DDL statements.
July 28, 2005 at 7:37 am
It can be. Profiler is basically doing the same thing except normally when folks use profiler they take one of the standard templates. Also, if you're not running profiler on the server itself, then the information is coming across the network (and even if you haven't specified writing to a trace file, Profiler is still writing to a temporary one locally).
K. Brian Kelley
@kbriankelley
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply