Home > Blogs > Don't mess with the system databases in SQL Server or you might get Error 916

Don't mess with the system databases in SQL Server or you might get Error 916

By  Aug 2, 2010

Topics: SQL Server, Data

It kinds of goes without saying (so of course I'm saying it) that unless you have a *really* compelling reason to change anything in the system databases you shouldn't. And by "system databases" what I mean are the big four:

1. master
2. model
3. msdb
4. tempdb

In some cases however - specifically in the security area - we (Microsoft) have been less than clear on the system databases. I want to address one particular issue that's been going around in discussions on the web, so I want to make sure I clear this up carefully.

Statement: Don't remove the "guest" account from the msdb system database.

Hopefully that's clear. Just don't remove it. It's not a bug that it's in there. You need to keep the guest account in msdb for LOTS of stuff to work, from Policy Based Management (PBM) all the way to SQL Server Management Studio. If you do remove it, you're apt to get this message (but only if you're not in the sysadmin group):

Failed to retrieve data for this request

Additional Information:

An exception occurred while executing a Transact-SQL statement or batch

The server principal "Buck" is not able to access the database "msdb" under the current security context. (Microsoft SQL Server, Error: 916)

I know, this is a very rare thing, and if you change something and then things quit working, you'll probably put 2 + 2 together to know what happened. But just in case an admin removes it and you can't access your databases through SSMS any more, well, there you go.

We DO have documentation on this: http://msdn.microsoft.com/en-us/library/ee342155.aspx, and we'll be updating the security best practices whitepapers we have to make this very clear. But since some guidleines tend to sound like you should remove guest from EVERY database, I wanted to make sure you know what to do in the meantime.

My friend Cliff Dibble, a Principal Program Manager on the same team at SQL Server I worked at, has provided us a script you can use to see if you have the issue:

/* Find the issue of 916 if result set is empty, you have the issue */
USE msdb;
SELECT prins.name AS grantee_name, perms.*
FROM   sys.database_permissions AS perms
JOIN   sys.database_principals AS prins
ON     perms.grantee_principal_id = prins.principal_id
WHERE  prins.name = 'guest' AND perms.permission_name = 'CONNECT';
/* Fix issue */
USE msdb;
GRANT connect TO guest;

So there you have it. Look for more clear guidance in our security tools forthcoming.

Note: If you're reading this more than a few months away from July of 2010, do more research. Never trust an old blog as gospel on anything, including my entries. Always refer to Books Online for the authoritative answer, and if it's wrong, file a bug against it using the "Feedback" Button.

Become an InformIT Member

Take advantage of special member promotions, everyday discounts, quick access to saved content, and more! Join Today.