Don’t mess with the system databases in SQL Server, or Error: 916

Don’t mess with the system databases in SQL Server, or Error: 916

Rate This
  • Comments 6

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.

 

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. (Microsoft.SqlServer.Manager.Sdk.Sfc)

Additional Information:

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

(Microsoft.SqlServer.ConnectionInfo)

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';

GO

 

/* Fix issue */

USE msdb;

 

GRANT connect TO guest;

GO

 

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

 

Leave a Comment
  • Please add 5 and 7 and type the answer here:
  • Post
  • If CONNECT for guest in msdb should never be revoked, shouldn't Microsoft change SQL Server to disable that capability entirely ?

  • Whats the advantage of disabling GUEST user in MSDB in SQL 2005?

  • Lee - it's complicated. DBA: There is no advantage. People do it because they think it's "secure" to do so.

  • One adv. that I can think of Buck is: Principle of least privilege. There is no reason why you would give all logins access to MSDB, which you HAVE to with sql 2008. Thousands of SQL 2005 servers have worked well with GUEST disabled in MSDB.

  • DBA: That's fine, with the exception that it will affect system access. You can refer to the articles for more information, and we'll release more in the future. Of course, at the end of the day, they are your servers, so do what you think you need to for security - but things will start breaking.

    Thanks for posting!

  • Thanks for replying Buck! In SQL 2008 I have no option but to give all logins access to MSDB on my servers.

    Why is Microsoft going the other way with SQL 2008, where you HAVE to keep the GUEST user enabled in MSDB giving access to MSDB for ALL logins?

Page 1 of 1 (6 items)