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