question

Sai-0641 avatar image
0 Votes"
Sai-0641 asked pituach commented

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

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?

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Sai-0641 ,

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

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered ErlandSommarskog commented

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 t*he 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 :-)

· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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?

1 Vote 1 ·

yes, please use the below query

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

0 Votes 0 ·

Hi @ErlandSommarskog

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.

143520-image.png

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









0 Votes 0 ·
image.png (16.0 KiB)

Good question @ErlandSommarskog

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

In the database itself.




0 Votes 0 ·

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.

0 Votes 0 ·

Thank you so much!

0 Votes 0 ·


You are most welcome :-)

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

If you had no suitable auditing set up, the answer is no, you can't.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered pituach edited

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.



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
0 Votes 0 ·

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





0 Votes 0 ·

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

0 Votes 0 ·

I published it @Sai-0641 :-)

have fun. I hope this is useful

0 Votes 0 ·
RonKlimaszewski-9878 avatar image
0 Votes"
RonKlimaszewski-9878 answered pituach commented

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Good day Ron @RonKlimaszewski-9878 ,

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.

0 Votes 0 ·

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.

0 Votes 0 ·


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

Yes (by default)

Short answer... not fit me :-)

0 Votes 0 ·

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.

0 Votes 0 ·