If you are lucky you’ve never seen an error along the lines of:

The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'. 

But let’s face it, luck runs out. So you may want to know what on earth this mysterious fifth system database is. As of SQL 2005 the source code for the system stored procedures, functions views etc. is stored in a resource database. If you guessed that the resource database is called mssqlsystemresource you would be correct. So why would Microsoft do this? Well it makes upgrading/patching SQL Server a much simpler task. Now instead of dropping and re-creating each of the updated system objects, which among other things can have some permissions implications that have to be dealt with, Microsoft just has to replace resource database.

Now if you look at my example above you might start thinking “Ok, sysobjects is now in mssqlsystemresource. So how come when I do my select it’s still master.dbo.sysobjects?” Simple enough. The objects within mssqlsystemresource are basically hidden behind the master database, and possibly the other system databases though I’m not certain about them. So in the case of sysobjects any reference to it is done through master not mssqlsystemresource. So to grant permissions on the sysobjects view you would grant it through master.

The next most common question that I see about this database (after where do I grant permissions) is how do I back up and restore it. Well, it can’t be backed or restored up using normal database backups and restores. If you want to back it up then you will need to do a file copy (or backup). This also means that restores are done by copying the file back into place (or do a file restore). If you have to do this make sure that the copy you are putting back in place is the same version (SPs, CUs etc) or you will need to re-patch your instance. So for example when you patch your instance you may want to make a new copy of the mssqlsystemresource files.

So what do you do if you find that your system resource has been deleted, corrupted, or didn’t get restored as part of a DR and you find you don’t have a backup? (Two of the previous have actually happened to me. I’ll let you guess which.) You now have two options. First and easiest, copy the resource database files from another instance that is the same version, service pack, cumulative update etc. Next (and last resort) is similar to what I discussed about the Model database. Use the REBUILDDATABASE option of setup. Here is the BOL link for 2012. It appears to be similar if not the same as the 2008 R2 BOL: http://msdn.microsoft.com/en-us/library/dd207003.aspx. If you use this method be forewarned that it over writes ALL of the system databases. Once you are done you will need to restore your master, msdb and model databases, and re-patch your instance in order to get back to where you need to be.

So see, now that you know more about the resource database, those of you who obsess about DR (most DBAs I’ve met) have one more thing to stay up nights worrying about.

You’re welcome.