Get the Creator

SQL Server Administration
1

Is it possible to get the user account who created a table? I was able to get the created date and modified date of a table by SELECT against the sysTable.

2

can you check the schema changes report under SSMS standard reports.

3

Thanks for the reply AhMeds, but can this be done by SQL code?

4

Perhaps this might help

1 Like
5

what is your use case here? Why do you need to find this out? what process is there that you cannot know this from a deployment process?

6

Hi, I am taking over a database for a former coworker. I need to track down all the tables that he has created.

7

why?

8

I need to move the tables that he created, to another database.

9

it sounds very peculiar process. basically you can do many things in SQL server but is it maintainable. You don't want to create processes that when you leave someone else does not have to do what you are doing. You want to implement best practice.

10

I am not sure your meaning when you wrote peculiar.

Basically, the vendor saved their tables in Database A. This former coworker also created his tables in Databases A. Now, he is no longer here, we don't use his tables and want to move his tables to another database so that when we do backup of the database, it is only the vendor's data. It is just a simple question can we track the creator of tables so that I know which is his. Otherwise, I just ask the vendor for their list of tables.

11

Have a look at

select * from sys.tables

If you're lucky and your co-worker created the tables AFTER the vendor created tables were created (or you can isolate specific date ranges) you might be able to extract the "new" tables by looking at create_date

12

peculiar meaning very unusual that you have a production system where people create tables that need to be removed later on. I would use the paranoid approach and have them create tables in an adjacent database. Were these tables created for testing purpose or what?

13

Thanks UberBloke. So there is no way of tying who the creator into the table. OK.

Yosiasz, correct. This is what happen when the company lets people who are not knowledgeable in the field working on production database.

14

how long was that person there with you? Maybe you can go back to the week before person came in backups and compare?

15

He was here for about 6 months and they let him go before I came in.

16

can you go back six months?

17

Did you try the link @btrimpop posted?

I found solution from this link very interesting https://social.msdn.microsoft.com/Forums/sqlserver/en-US/847751bd-ca77-4ee4-aaf0-1222bf58228a/schema-changes-history-by-tsql?forum=transactsql

Something like this:

declare @curr_tracefilename varchar(500);
declare @base_tracefilename varchar(500);
declare @indx int;
select @curr_tracefilename=path from sys.traces where is_default=1;
set @curr_tracefilename=reverse(@curr_tracefilename);
select @indx=patindex('%\%',@curr_tracefilename);
set @curr_tracefilename=reverse(@curr_tracefilename);
set @base_tracefilename=left(@curr_tracefilename,len(@curr_tracefilename)-@indx)+'\log.trc';

select ObjectName
      ,DatabaseName
      ,StartTime
      ,EventClass
      ,EventSubClass
      ,ObjectType
      ,ServerName
      ,LoginName
      ,ApplicationName
  from ::fn_trace_gettable(@base_tracefilename,default) as a
 where EventClass=46
   and EventSubclass=0
   and DatabaseID<>2
   and DatabaseName='your_database'
   and ObjectType=8277
1 Like
18

Hi Bitsmed, yes, I read the link that BTrimPop posted but was trying to make sense when it got to "CASE ObjectType

        WHEN 16964 THEN 'DATABASE'"