1

I'm doing a compare of my development server with production and find one has this:

EXEC sp_grantdbaccess N'guest'

How would anyone take advantage of the guest user in a connection string? It doesn't seem to be mapped to a logon. In pubs I see guest is a member of public. Guest doesn't seem to be deletable.

Is this some sort of anonymous access account or what? If this is a security problem, what do I do to fix it?

Alex Angas
  • 2,017
  • 2
  • 26
  • 37
MatthewMartin
  • 309
  • 1
  • 6
  • 14

2 Answers2

4

If a login to SQL Server has no other way into a database, the guest user permits that access. You must first have a valid login to SQL Server. Whatever permission is granted to guest, that login will have the rights to do. There are a couple of things to look at:

  • What permissions does guest have?
  • What permissions does the public role have (all users, including guest, are a member of this role)?
  • What roles is guest a member of and what permissions do those roles have?

The general advice is not to enable the guest user. If a login should have access to a database, it is better to grant explicit access. That makes it easier to audit. With that said, you will notice that the system databases master and msdb have the guest user enabled if you take a look. This is a requirement.

K. Brian Kelley
  • 9,034
  • 32
  • 33
  • In enterprise manager, guest is listed as a user via group membership. EM reports the user doesn't exist when you try to drop it. It is not listed as a member of public. It has no explicit rights on any object. It hasn't been added as an explicit member of any role. Was the public role and guest account created just to create the illusion of complete and utter anonymous access without actually doing so? – MatthewMartin May 18 '09 at 22:53
  • 1
    Guest is a special user. So a lot of the normal user rules don't apply. For instance, in SQL Server 2000 (since you said Enterprise Manager), if you query sysusers on a database where guest is *not* enabled, you'll see that it's still listed. In SQL Server 2005 and 2008, membership in the public role is explicitly shown. I can't recall for SQL Server 2000. However, guest is a member, because all database users are: http://msdn.microsoft.com/en-us/library/aa905167.aspx – K. Brian Kelley May 19 '09 at 02:58
  • 1
    In SQL Server 2005, the default USER for a LOGIN that is not mapped to a database USER is the Guest. As Best Practice, you should always 'map' a LOGIN to a database USER. :-) – MarlonRibunal May 21 '09 at 18:25
  • 1
    Marlon is right. If Guest is enabled they now have access to a database they shouldn't. Otherwise, there is no mapping. Thhen they don't enter the database. :) – K. Brian Kelley May 21 '09 at 19:48
1

More than data deletion or other malicious activity, the first thing that comes to mind is accessability of your data by unauthorized users. For some attackers, sometimes it's not about what you can destroy, but what you can copy onto their system(s). If they can replicate any sensitive data you have, then it doesn't matter if they try to come back - after all, they have their own copy! The horse will pretty much be out the barn door in this case, and sealing the breach after the fact only stops other attempts, not the replication of data already taken.

Avery Payne
  • 14,536
  • 1
  • 51
  • 88
  • I'm actually asking about the default behavior you get when you database in enterprise manager. It seems to always have dbo, guest and public listed and users and roles. However, I've never been able to leverage guest and public to hack into anything as you seem to suggest is possible. – MatthewMartin May 18 '09 at 22:55
  • Then my answer is out-of-context; I thought you were talking about an older install that had the accounts already there. If you like, I can delete the answer. – Avery Payne May 18 '09 at 23:42