Guest account in User Databases

Guest account in User Databases

Rate This
  • Comments 1

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 3 and 8 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

Page 1 of 1 (1 items)