אוק22
Written by:
ronen ariely
22/10/2021 05:36 
Introsuction
This post presents a step-by-step answer to this question from Microsoft QnA forums:
Question: 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...
Note: There was a second question, but this post cover the first one.
For the sake of the discussion let's CREATE DATABASE
create database FakeCratorDB2
GO
Answer
Option one (DO NOT USE IT!) :
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.
SELECT suser_sname(owner_sid), * FROM sys.databases
WHERE [name] = 'FakeCratorDB'
GO
Well... I got my name, which make sense :-)
But as I mentioned, this is NOT the solution and you should NOT count on this!
-- Changing the owner of a database
ALTER AUTHORIZATION ON DATABASE::FakeCratorDB2 TO [Login345];
GO
SELECT suser_sname(owner_sid), * FROM sys.databases
WHERE [name] = 'FakeCratorDB2'
GO -- this will return Login345 and not the original LOGIN that created the database!
Option two: the right way but undocumented advance solution
Unfortunately, the information about the LOGIN who executed 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...
Question:
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 which I wrote several years ago to find who deleted a row from the table. The solution is almost the same, except that instead of searching for the action "LOP_DELETE_ROWS" we need to search for the action "LOP_CREATE_PHYSICAL_FILE"
https://ariely.info/Blog/tabid/83/EntryId/154/SQL-Server-Who-deleted-my-record-last-time.aspx
Step-By-Step
Note! for the sake of the solution, I will use the un-documented function fn_dblog in order to read the current transaction log file. If the information is old and stored in a truncation log file backup then you can use the function fn_dump_dblog instead.
Note! This solution is also does not guarantee to get the original creator. It based on the assumption that the main database file was was created with the database.
Update comment: it is important to understand that if you want to get old information and your database in simple backup mode then you do not have backups of your transaction log. In this case the information which was written in the transaction log, is simply not available. Note that this function should be executed in the database which we want to explore. (Thanks to Erland Sommarskog on this comment)
Before you start, please execute the following query and check how the information in the is presented. Our goal is to dive in the information and find what we need.
SELECT * FROM fn_dblog(NULL, NULL)
ORDER BY [Current LSN] DESC
GO
Step 1: find the full name of the database file
SELECT s.[name], f.physical_name
FROM sys.master_files f
INNER JOIN sys.databases s on s.database_id = f.database_id
WHERE s.[name] = 'FakeCratorDB2'
GO
Copy the full path to the data file for example (we can do the same with the transaction file).
In my case the result is:
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\FakeCratorDB2.mdf
Step 2: find the [Transaction ID] which executed the CREATE DATABASE
When we create a new database then the server execute several actions behind the scenes
(1) Any transaction starts with the action 'LOP_BEGIN_XACT'.
Therefore, this is useless at this time in order to find the right transaction, which created the DATABASE we are looking for. This is very useful to map all the actions which are related creation of the databasefor the sake of the learning.
(2) When a row is inserted to the database, then the action `LOP_INSERT_ROWS`is written in the transaction log.
When you create database, then first of all the server need to write the information in the master database, which is why you next to starting the transaction, you will see a few `LOP_INSERT_ROWS`actions.
(3) Next, the server can start build your new database. Now, you get to the intresting action. When the file is created the the action `LOP_CREATE_PHYSICAL_FILE` is written to the transaction log.
This is the one we should search, using the database file name and location which we found in step 1.
SELECT * FROM fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_CREATE_PHYSICAL_FILE' and Description = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\FakeCratorDB2.mdf'
ORDER BY [Current LSN] DESC
GO
If we could get the "[Transaction SID]" then we finished our work as this is the user which created the DATABASE, but unfortunately the value in this column is NULL. We will need to keep working to get this information from different record.
If you got only one row in the result, then we are good to continue. In this case you have found the record in the transaction log which stores the action of creating the file.
Unfortunately, you cannot count on this if you got more than one file. This means that the same file name and location was previously created and you found multiple actions. In this case, we need to choose which is the right record for our case. We can use the information about when this record was written, but if you check the column "[Begin Time]", then you will notice that it is NULL. Same as with the [Transaction SID], we will need to get this information from another place for each one of the rows, in order to find the which is the real time of each action.
Not an issue but more work to do...
Our goal in this step was only to find the "[Transaction ID]"
In my demo the I got: "0000:00007065"
Step 3: Find the [Transaction SID]
And back to the transaction log file... We need the first action...
Do you remember that I said that each transaction start with the action "LOP_BEGIN_XACT"? The information of the "[Transaction SID]" which is the LOGIN ID and the "[Begin Time]" are stored in the record of the action "LOP_BEGIN_XACT". We will use the transaction ID in order to find the fitting LOP_BEGIN_XACT record.
SELECT [Transaction SID], * FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = '0000:00007065' and Operation = 'LOP_BEGIN_XACT'
GO
Final step 4: find the LOGIN
That's all
Using the [Transaction SID] you can find the LOGIN which created the database
SELECT SUSER_SNAME(0x010500000000000515000000B5A1BF0A4B5FE04DEEF2D67FF4010000)
GO
What the F ?!?
It was me that CRETAED the database
You could simply asked me 🤣 LOL