Querying Secure Information from a Restored Master Database Backup

I recently had someone pose the following question to me: "Is it possible to collect login/security information from a backup of the master database?" Since I'm always looking for topics to write about here I wanted to answer that question here.

It's Just Another Database... Or Is It?

You can take a backup of master, model, or msdb databases and restore them as you would any other user database under a new name and file location as a copy of the original. While you can't apply extended stored procedure commands to the restored copies of the system databases (xp_login_info for example will run against the true system databases) it would appear you can directly query the databases for the information they store. Let's see if that's really the case.

Below I'll go through the process of backing up the master database as a privileged user with rights to do so:

--BACKUP MASTER DATABASEBACKUP DATABASE [master] TO  DISK = N'C:DataMSSQL12.MSSQLSERVERMSSQLBackupmaster_foo.bak' WITH  COPY_ONLY, NAME = N'full master backup copy-only', COMPRESSION,  STATS = 100;

Then I'll go through the process of restoring the backup of master as a copy. This will create a user database called copy_of_master. To highlight vulnerabilities when you don't properly secure the location of your backup files or protect your data or backups while at rest I'm going to restore this backup on a different SQL Server instance using a SQL login that had no rights on the instance from which the backup of master was derived:

--RESTORE AS A COPY OF MASTER DATABASEUSE [master]RESTORE DATABASE [copy_of_master] FROM  DISK = N'C:DataMSSQL12.MSSQLSERVERMSSQLBackupmaster_foo.bak' WITH  FILE = 1,  MOVE N'master' TO N'C:DataMSSQL12.MSSQLSERVERMSSQLDATAcopy_of_master.mdf',  MOVE N'mastlog' TO N'C:DataMSSQL12.MSSQLSERVERMSSQLDATAcopy_of_master.ldf',  --NOUNLOAD,  STATS = 100;GO--CAN YOU QUERY FOR LOGIN INFO ON COPY?SELECT name , sid , type , type_desc , is_disabled , default_database_nameFROM copy_of_master.sys.server_principalsWHERE type_desc != 'CERTIFICATE_MAPPED_LOGIN'ORDER BY type_desc;

That accomplished let's first look at the ownership of that restored database:
 

--CONFIRMING RESTORE AS NON-PRIVILEDGED ACCOUNT/* Only has ownership over the restored copy of master*/SELECT DB.name, SP.name AS owner_name FROM sys.databases AS DB INNER JOIN sys.server_principals AS SP ON DB.owner_sid = SP.sidORDER BY DB.name;--WHAT RIGHTS DID RESTORING LOGIN HAVE ON INSTANCE?/* Can only see the restored DB it owns, the system databases, and the login's default database.*/SELECT name , default_database_nameFROM copy_of_master.sys.server_principalsWHERE type_desc NOT IN ('CERTIFICATE_MAPPED_LOGIN', 'SERVER_ROLE')ORDER BY name;/* Only server role membership was db_creator to afford restore ability*/SELECT LOGINS.name AS login_name, ROLES.name AS role_nameFROM sys.server_principals AS LOGINSINNER JOIN sys.server_role_members AS ROLE_MEMBERSHIPON LOGINS.principal_id = ROLE_MEMBERSHIP.member_principal_idINNER JOIN sys.server_principals AS ROLESON ROLE_MEMBERSHIP.role_principal_id = ROLES.principal_idWHERE LOGINS.name = 'NotIt';

At this point this confirms that I successfully took an existing backup of master and restored it as a copy of the master database on another instance with a login that only had db_creator permissions on the instance. This also highlights that when you restore a database it will be owned by the login it was restored under by default. It's interesting to note here as well tha your query results against system tables you are granted implicit rights to as a member of a system role (in this case just db_creator) are filtered to just those databases/permissions therein that you've rights.

Now at this point you have a copy of someone else's master database that you can now query against for any metadata stored within it.

Tell Me Some of Your Secrets

The original question was whether it was possible to restore a copy of master and then query for login information. I'll address that first with the following query:

--CAN YOU QUERY FOR LOGIN INFO ON COPY?SELECT name, type, type_desc, is_disabled, default_database_nameFROM copy_of_master.sys.server_principalsWHERE type_desc NOT IN ('CERTIFICATE_MAPPED_LOGIN', 'SERVER_ROLE')ORDER BY type_desc;

At this point I'm executing this query as the lower privileged "NotIt" login. Let's look at the results of this query with that understanding:

Results as login with only db_creator role permissions

It "appears" as though we're seeing results from a query against the sys.server_prinicpals system table in the restored copy of master from the other SQL Server instance. However something isn't right here. Do you see it? The NotIt login didn't exist on the other instance - the one from which the backup of master is sourced. Since I'm curious I'll re-run this same query logged into this second instance with sysadmin permissions which grant me access to all the metadata hosted in any user and system database. When doing so these are the results we receive:

Results as login with sysadmin role permissions

This is interesting isn't it? The NotIt login owns the restored copy of the master database and therefore should be able to gain access to all data stored within the database. However it's quite obvious something is not quite right because the results returned not only don't show all results stored in the database but also include the NotIt login that clearly didn't exist on the instance where the master database backup is derived.

Why?

This goes back a few years: to 2005 matter of fact. That was when the master database ceased being the source-of-truth for much of the instance metadata and was replaced in that role by the resource database which end users, not even sysadmin role members, can query directly. What you're seeing in these results is the behavior that now exists within the master database: that what appear to be listed as system tables are in fact views that are sourced from the resource database. This means that even though you restored master from another instance when you query the "tables" we did above: sys.databases, sys.server_principals, sys.server_role_members you're in fact querying system views that source back to the current instance's resource database. That is why you only see results you're rights allow you to see which in this case are just the always-exitsing sa and the current login: NotIt. It also explains the existence of the login that didn't exist on the original instance as well as why a more privileged account can see more results with the same query.

What About "Other" Data in Master?

This covers the behavior with any and all metadata sourced from the resource database. But what about those violations of Best Practice when database administrators create objects in the master database such as user tables and stored procedures?

Before I took a backup of the master database I created some tables for my son Trevor's marching band's uniform database I'm building as a hobby. Consider the list below when I run it against the original master database as a sysadmin:

SELECT name, type FROM master.sys.objects WHERE type = 'U' ORDER BY name;

User objects in the original master database

Take note that there are a few tables that are system-created that are classified as user tables. (Thanks for confusing things for us Microsoft!) Regardless, since these are all classified as user-created tables in a database we restored AND the NotIt login is the owner of the restored master database (as copy_of_master) we should be able to see these same objects when we query the restored copy as the NotIt user, right? Let's run the same query as above but against the copy_of_master database on the new instance:

SELECT name, type FROM copy_of_master.sys.objects WHERE type = 'U' ORDER BY name;

User objects visible to db_owner

Even though we ran this query as the owner login for the copy_of_master database we can't see those objects created by user(s) against the master database on the source instance of SQL Server - just these outlier system tables masquerading as user-created tables. Is it possible to query the tables directly?

SELECT * FROM copy_of_master.dbo.Coats;

Even though the login is owner of the database it has not been granted implicit permissions on the user tables. This does not prevent a login with rights to grant permissions from doing so on this new instance however. If the user who has restored the copy of master to the new instance has rights to do so they could easily see all the user-created objects just as they could with any non-system database.

Conclusion

So the answer is "No". Even though you can restore a copy of master and query against it. You not only can't see complete login information since it's a system view populated from the current instance's resource database but you're also precluded from seeing user-created objects and querying against them until you're granted the proper rights to them through the regular means on the new instance by a login with the rights to do so. You should always take precautions to properly secure any and all backups for your databases because as you can see here anyone with the ability to get to your databases can restore them elsewhere and eventually gain access to the secrets they contain if given proper rights.