Guest account in User Databases

Guest account in User Databases

Rate This
  • Comments 4

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.

Leave a Comment
  • Please add 4 and 6 and type the answer here:
  • Post
  • Thanks Jack, for taking making it public here at MSDN.

    I would like to add a different view at the problem. We both agree, that a professional SQL Server Admin would not use guest. And probably none of our dear readers here would do that - because they already care enough about security to inform themselves over the internet.

    But, there are a lot of not so well educated Admins, so called "Accidental Admins" that take care of SQL Server Implementations.

    The success of SQL Server has a lot to do with the ease of use - one of the domains of Microsoft. SQL Server is easily set up and run and can handle a lot of important data without rocket-science knowledge. And this is a good thing.

    I am sure you would agree on that, too.

    And now, what do people learn first, when it comes to security? Among the first things I remember having learned are things such as "If a principal is member of a group which has a permission, a deny for the individual principal overrides the group’s permission".

    Knowing that, I believe the following scenario is not so unrealistic:

    "I want almost anyone from my company to access a couple of databases.

    Instead of adding all accounts to the databases, I just allow access for everyone by using the guest-account.

    For some exceptions like our trainees I will add them using the "trainees-group" and click “DENY CONNECT”. I tested it, it worked: they cannot access this database.

    I also allow them to use a certain database, where they can test things, play around, which is also open to anyone (using guest for simplicity)."

    The DBA is not stupid. He relies at least partly on groups. He uses deny to be sure from exceptions.

    Of course a professional knows that a "whitelist" is better that "blacklist" - in other words, to add regular employee-groups to the "kind of public" databases and keep deny as a last resort.

    But.. the guest account exists. Why not use it for such an obvious scenario to save time creating database-users and roles. At first sight this actually is a "keep it simple" implementation, isn't it?

    And, honestly, how many professional Admins would actually KNOW that this is NOT safe - when looking at it and testing it quickly it works as intended, so why changing it - maybe later when there is time.

    This is not about saying SQL Server is unsafe because of it. We certainly won’t see a major break-in though guest-access. But there are a lot smaller companies with DBAs that do a lot more than just databases. So basically I am saying: this creates an exception to the rule “a deny overrides any other permission”.

    The fact that the user at the target-database is not "himself" anymore, but guest already - because he came from a different database with guest enabled and can then do another impersonate to become “local guest” - is difficult to comprehend.

    Most aspects in SQL Server Security work that well, because they are simple: no grant -> no access, within group with grant -> access, within group with grant but deny from other group -> no access

    The one (hopefully well known) exception: sysadmin

    -> simple, straightforward (I love it)

    That's all I wanted to add. Hopefully one of the accidental DBAs stumbles over this. :-)

    By the way – in this connection I would be really interested, what the deeper technical reason might be that with the next version of SQL Server this actually will work differently. Certainly not by accident I believe. Although this time I would not protest :-)

    Andreas

  • 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...

  • Uncle Bob - couple of things.

    1. Application Roles were never intended to work cross database - don't expect them to ever start them to work cross databases.

    2. 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.

    3. 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.

  • yes, i am agree with this..........

Page 1 of 1 (4 items)