Some days ago, a simple request came from the development team for one of our customers. Simply said, it was something like:
Hey DBAs, give us read and write permissions on database X. We want to do what we do best, and we want to do it immediately!
Now, what I did was to add the domain group which was “holding” the domain accounts of the dev guys to the SQL Server and grant it the needed permissions for the requested database – DevGuysCannotSeeMe.
A moment later, one of them is sending us a screenshot looking similar to this one:
Going back, I do remember that I have granted permissions on database DevGuysCannotSeeMe(by the way, we are talking SQL Server 2008 R2 here), but I haven’t granted them any on msdb. The interesting part, however, is why do I need to grant them permissions to msdb at all and will this fix the issue?
In the mean time, you can see that the dev guys can do what they want to do with their DB, because they can query it, but they just get this strange error when expanding the Database tree in the Object Explorer.
The fix: After a quick look and some time spent investing this, something very interesting was found – the guest user in msdb was actually disabled (someone has run this code without knowing what the consequences will be).
USE msdb GO DENY CONNECT TO guest; GO
That’s fine, I thought myself, but what’s the problem indeed? The problem, ladies and gentlemen, is that when the guest user is disabled in msdb and if you are not assigned with sysadmin rights in the SQL Server instance, you will have hard time seeing the user databases. Microsoft has mentioned it here -> http://support.microsoft.com/kb/2539091.
And by the way, you can give the Developers whatever permissions on msdb you want, even assign them to the db_owner role – this will not help you. It just won’t. So, next time you want to follow Microsoft’s security best practices (as disabling the guest user in the USER databases is one of them), be careful not disabling the guest user in any of the sys databases, as someone will face some really, really interesting and unexpected errors.
I am out! 🙂