THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Buck Woody

Carpe Datum!

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

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.

 

Published Monday, August 02, 2010 7:41 AM by BuckWoody
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Pradeep Adiga said:

Nice article !

Thanks,

Pradeep Adiga

August 2, 2010 11:15 AM
 

Abhilash Melethil said:

Good one!

Thanks,

Abhilash

August 3, 2010 2:25 AM
 

Seth Lynch said:

Thanks for that Mr Woody; I have now amended my lock down script!

August 3, 2010 7:32 AM
 

Reddy Balaji C said:

Usefull post ... Thank you

August 5, 2010 8:07 AM
 

DBA said:

Wow, Why did you remove mine & the other guys comments from here?

August 9, 2010 8:56 AM
 

DBA said:

August 9, 2010 9:08 AM
 

Buck Woody said:

DBA - No - I don't do that. In fact, I think I just responded to them last week - not sure what happened. I always let things stand unless someone gets rude to another person (not worried about myself) or racist or something like that. Woulnd't touch them otherwise.

August 9, 2010 10:57 AM

Leave a Comment

(required) 
(required) 
Submit

About BuckWoody

http://buckwoody.com/BResume.html

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement