Guest account in User Databases

Andreas Wolter recently posted yet another reason to keep guest disabled on user databases in SQL Server. He also points out some reasons why developers shouldn’t have access to production systems, but I’d like to focus on the implications for guest. As Andreas summarizes at the end of his post,

“never use the guest account for data that is not really supposed for everyone .”

Absolutely agree! Guest is disabled by default in all user databases and should remain so – guest really does mean everyone. There is no way to keep someone that has access to SQL Server from leveraging an enabled guest account – that is how guest is designed to work. No planned changes will alter this guidance. If you need broad access to a database but with some exceptions, it is preferable to use Windows group accounts with broad membership to provide that access and then deny as needed. For SQL authenticated users, explicitly provision the individual logins which need access.

I should also point out that guest is needed for the proper functioning of some of our system databases – such as tempdb. But here the situation is that everyone on the SQL Server instance really does need access to this database for temporary objects. See Buck Woody’s post Don’t mess with the system databases in SQL Server, or Error: 916 for more information.

Comments

• Anonymous
  September 24, 2010
  The comment has been removed

• Anonymous
  April 18, 2014
  Please correct me if I am wrong! Guest access allows you to establish a connection to an instance of a SQL server. However, a guest connection is only established when a SQL login user has NO user mappings to ANY of the databases on that instance. ie. A SQL Login with no database mappings, will result in a guest connection, with the access equal to what has been assigned to the guest account. (which might be connect only).  Documentation is sketchy to say the least, but please tell me if there is ANY other way to actually intentionally connect to a SQL instance using the guest account directly? Otherwise, I really do not see what all the hype is about enabling the guest account. Also, to be able to  use SQL Server Application roles and access info across databases, the only way to do this is to enable the guest account on the other database(s) so that the application role can live vicariously through it. If you ask me, this was a major design flaw in that not only can application roles not be passed between databases without enabling the guest account in the other database(s), it also effectively limits application roles to single databases if company policy is not to allow guest accounts. If you regularly ensure that there are no "orphaned" SQL logins that have no databases mapped to them, then IMO I don't see a problem if the guest account is active...

• Anonymous
  April 18, 2014
  Uncle Bob - couple of things.Application Roles were never intended to work cross database - don't expect them to ever start them to work cross databases. Although you can't login into SQL as GUEST, it is common for people to create LOGIN accounts for very broad Active Directory Groups. Once connected to SQL, anyone can impersonate GUEST account and connect to any database with GUEST enabled. Technically, if you never DENY a permission to anyone, guest may be okay (assuming you are okay with anyone accessing that can access SQL Server). But this is the type of thing that people forget or doesn't transition when people move between groups or leave companies and mistakes get made (new Group login gets added or a DENY permission is added with intent to block access on DB with Guest enabled). As a design principle, we tell people don't enable GUEST in user databases because it gives everyone a second identity that may have more permissions than their primary identity.

• Anonymous
  July 20, 2014
  yes, i am agree with this..........