An interesting use case came up recently about misusing databases keys by piggy backing off the DBID of another database that already exists. This is a theoretical exercise where we try to misuse the fact that DBIDs are reused within SQL Server. The first thing we need to do is confirm the DB IDs are being in fact reused. The steps to recreate this behavior is shown below along with screenshots.
STEP 1
First create a new database and have a DBID assigned to it. In this case we can see the DBID is created with the ID =14
Next, we drop the database and confirm it’s been removed from the server. In this case we are expecting SQL to automatically assign a DBID to the new database that has been created. The question is will it assign an ID if 15 since the previous DB was 14 and then dropped or will it reuse the ID 14 without skipping it.
STEP 2 When we recreate the database; we can see that it automatically created the database with the ID 14 again. So, this confirms that they database IDs are not assigned in a logic similar to the use identity columns where the id value is skipped with the data has been deleted.
STEP 3 To further drive the point home, we try one more scenario. What if there are gaps? In this case we create 3 databases with Ids 1,2,3 , then delete the one with the Id 2 and check if 2 gets reassigned on creating a new DB.
First, we create databases DBA, DBB and DBC
STEP 4
Next, we drop database named “DBB”, the expectation is that DB with the ID 16 is no longer present in the server.
We recreated the database “DBB” and seems the Gap is filled as the DB is created using the 16 , so it took the same id as before within the GAP and not the ID 18 which would be the next available Identity column.
This behavior is confirmed when we create a new database with a different name that didn’t exist before.
At this point we know that DBID cannot be trusted to uniquely identify the correct database. For example, I can create a database called example and create server level objects such as logic and keys against it. Once the database is dropped these server level objects are orphaned. The most example of this is the presence of orphaned users in a DB after a restore etc.