How find who created database and from which system it was created?

Sai 126

I have seen new database in my server. that was created nearly 45 days back. I have tried to check using standard report but no luck.

SQL version: SQL server 2017 enterprise.

Is it possible to find who created database and from which system it was created?

Seeya Xi-MSFT 16,436 Reputation points

2021-10-25T07:34:49.01+00:00

Hi @Sai ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

Accepted answer

Ronen Ariely 15,096 Reputation points

2021-10-22T03:32:08.84+00:00

Good day Sai :-)

from which system it was created?

For this we will need to use the first step and cross information with the operating system information and the active domain and so on. Fot he scope of this trhead I will present the full answer for the first part of the question which is related to the work of the (very advanced) DBA.

How find who created database?

(1) It is very simple to get the owner of the DATABASE, which in many cases (especially in small companies) will probably be the same as the LOGIN who created the DATABASE, but the owner of the DATABASE is not necessarily the LOGIN who created the database since someone might have ALTER AUTHORIZATION for databases and change the LOGIN to another LOGIN.

Unfortunately, the information about the LOGIN who execute the creation of the database, is not stored in the database.

but no worry, this is why we are here :-)

SQL Server stores the information about DDL actions like CREATE DATABASE, simply not in the database. Pulling the information is a bit more complex and requires a bit of internals knowledge and parsing of data and using an undocumented tool. It is not that complicated actually.

So... where SQL Server store information about DDL actions?

Answer: In the transaction log obviously :-)

And this is where we can get the information about the real LOGIN which created the database, assuming you have all the backups of the transaction log.

Here is a nice post I wrote several years ago to find who deleted a row from the table. The solution is exactly the same except that instead of searching for the action LOP_DELETE_ROWS we need to search for the action ``

https://ariely.info/Blog/tabid/83/EntryId/154/SQL-Server-Who-deleted-my-record-last-time.aspx

I will write a fast blog step by step with full demo in a few XX (I have no idea how long it will take :-) But you have all the information and I just want to present full demo)

UPDATE: Step-By-Step full demo!

I published a full demo from the CREATE DATABASE to finding who created the database

https://ariely.info/Blog/tabid/83/EntryId/303/SQL-Server-Who-created-the-database.aspx

I hope this is useful :-)
Please sign in to rate this answer.
Erland Sommarskog 100.8K Reputation points MVP

2021-10-22T22:15:20.757+00:00

That's a piece of work, Ronen!

But I do see a small problem here: Sai said that this happened 45 days ago, and most likely the transaction log has been truncated since then. So Sai would have to find a log backup. And if there is simple recovery...

One thing is not really clear to me. Should I run the query in the database itself or in the master database?

Sai 126 Reputation points

2021-10-25T08:18:56.38+00:00

Thank you so much!

Sai 126 Reputation points

2021-10-25T08:22:20.027+00:00

yes, please use the below query

SELECT suser_sname(owner_sid), * FROM sys.databases
WHERE [name] = 'DBname'
GO

Ronen Ariely 15,096 Reputation points

2021-10-25T18:51:08.107+00:00

You are most welcome :-)

Ronen Ariely 15,096 Reputation points

2021-10-25T18:57:14.8+00:00

Hi @Erland Sommarskog

That's a piece of work, Ronen!

Thanks :-)

But I do see a small problem here: Sai said that this happened 45 days ago

I explicitly explain the blog what to do in this case. The solution is the same, except that instead of using the function fn_dblog on the live file, you need to use the function fn_dump_dblog on the backup file.

And if there is simple recovery...

THIS IS GREAT POINT :-)

Obviously we cannot cover this case. I will add this comment to the post. Thanks

Ronen Ariely 15,096 Reputation points

2021-10-25T19:17:32.113+00:00

Good question @Erland Sommarskog

Should I run the query in the database itself or in the master database?

In the database itself.

Erland Sommarskog 100.8K Reputation points MVP

2021-10-25T20:41:06.683+00:00

I explicitly explain the blog what to do in this case. The solution is the same, except that instead of using the function fn_dblog on the live file, you need to use the function fn_dump_dblog on the backup file

I did notice that part. But if it happened 45 days ago, you take log backups every 15 minutes like a good boy should, you need to know which backup it was.

But, wait, if this the transaction-log dump of the database itself, it must the first one. Silly me! ...except, until you have taken the first full backup of the database, the database is effectively in simple recovery, so there is some fair chance that the information is lost quickly.
Sign in to comment

3 additional answers

Erland Sommarskog 100.8K Reputation points MVP

2021-10-21T21:59:37.75+00:00

If you had no suitable auditing set up, the answer is no, you can't.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Seeya Xi-MSFT 16,436 Reputation points

2021-10-22T02:21:03.457+00:00
Hi Sai-0641,

You can use the following script to find who created database.

SELECT suser_sname( owner_sid ) AS 'Creator', * FROM sys.databases

But could you describe in detail what you mean about "which system" ？

Best regards,
Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
Ronen Ariely 15,096 Reputation points

2021-10-22T02:49:04.107+00:00

DO NOT COUNT ON THIS!

This provides the LOGIN which related to the owner of the database and NOT necessarily the LOGIN that created the database!

You can ALTER AUTHORIZATION for databases and change the LOGIN to another LOGIN and your result will show the new one and NOT the creator

create database FakeCratorDB GO -- Changing the owner of a database ALTER AUTHORIZATION ON DATABASE::FakeCratorDB TO [Login345]; GO SELECT suser_sname( owner_sid ) AS 'Creator', * FROM sys.databases GO

Sai 126 Reputation points

2021-10-22T03:33:56.223+00:00

Can we find database created user system name?
Who created new database and their system name?

Ronen Ariely 15,096 Reputation points

2021-10-22T03:55:36.3+00:00

We cannot know which person connected the server but who is the LOGIN and when he did it, we can...

For the OS/domain user (not necessarily the specific person since maybe multiple people uses the same account), you will need more crossing of information

I am in the middle of writing the blog for you :-)

check my answer below in the meantime and forget the solution of using the sys.databases as I explained above

Ronen Ariely 15,096 Reputation points

2021-10-22T05:35:54.087+00:00

I published it @Sai :-)

have fun. I hope this is useful
Sign in to comment
Ron Klimaszewski 1 Reputation point

2021-10-22T13:45:11.687+00:00

This information is in the default trace (or one of the rollover files). Search for [EventClass]=46, [EventSubclass]=0, [DatabaseName]='whoCreatedMe'. If recent enough, you could use the built-in Schema Changes report.
Please sign in to rate this answer.
Ronen Ariely 15,096 Reputation points

2021-10-25T19:35:39.81+00:00

Good day Ron @Ron Klimaszewski ,

I highly hope that you do not have the trace activated when you do not explicitly need it! You should not using monitoring like Extended Event constantly since it cost resources and can reduce performance. I recommend you to re-check all your servers and disable any un-needed monitoring.

Erland Sommarskog 100.8K Reputation points MVP

2021-10-25T20:37:03.963+00:00

I highly hope that you do not have the trace activated when you do not explicitly need it!

So you are suggesting that the default trace should be disabled? If so, I disagree. While the information in the trace is fairly short-lived due to the limitation size and rollover files, it can still be useful in some situations.

Ron Klimaszewski 1 Reputation point

2021-10-26T11:03:15.15+00:00

Microsoft thought it to be important enough and inconsequential in resource usage to have it enabled by default, therefore most will leave it on. This is another albeit less elaborate method of finding who created the database (or made any modifications).

In 15 years and over a thousand instances, I have never had an occasion where the default trace caused an issue. If this trace causes a performance issue, then the server is undersized or a database needs to be optimized.

Ronen Ariely 15,096 Reputation points

2021-10-29T20:48:47.06+00:00

So you are suggesting that the default trace should be disabled?

Yes (by default)

Short answer... not fit me :-)
Sign in to comment

How find who created database and from which system it was created?

UPDATE: Step-By-Step full demo!

3 additional answers