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:
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)
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 */
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 */
GRANT connect TO guest;
So there you have it. Look for more clear guidance in our security tools forthcoming.