SQL SERVER – Disable Guest Account – Serious Security Issue

“No Guests PLEASE!”

“Doesn’t your Indian tradition suggest welcoming guests and treating them in the best way possible?”

“Yes, but I am talking about the Guest user in SQL Server.”

Oh!”

This was a real conversation that happened a couple of years ago. I welcome guests as much as any other Indian does; however, I am strongly opinionated about guest user in SQL Server. I like to keep it disabled unless there is a special need of it. If there is some persistent need of a guest user, I suggest to create separate account. Again, there are always special cases where there is a need of this guest account. But in other cases, a guest account is not necessary.

A Real World Story

Today I am in the mood to tell a real life story.

About 2 years ago, I got a call very late in the evening from one of my friends. He said that his database was compromised and so he needed to talk to me. He described the situation to me – that his database had a single user (himself), but without his knowledge, his database was often observing non-permitted activities. He was very scared.

When there is no real clue, most companies take the database down or turn it offline to prevent any further damage to the system.

Just like the same, he decided to disconnect the database from other applications, as well as to move th behind firewall of the system. After making sure that there is no other application connected even at behind the firewall, there was still this inappropriate activity going on. He became more worried, and finally he sought my help.

I decided to take up the challenge to help him. After a while, we discovered that he had the GUEST user enable on his database. Even though he had already removed all the other accounts, he missed to remove the guest user account. As the guest user was enabled, any user who had permission to server was able to get into his database. Well, after a careful review, we realize that one of his co-workers was pulling the prank on him. He had given additional permission to his guest user which leads any user logging into server got admin rights to my friend’s database.

Upon figuring out this issue, we disabled the guest account and the problem was resolved. To complete the story, the guy who played the prank on my friend was moved to another department (almost as good as fired) where he could not touch the production database.

Reproduction of the scenario

After remembering the story, I decided to recreate the scenario here. Please note: do not play with Guest Account without fully understanding its implications.

USE AdventureWorks
GO
-- Note: NEVER run this on production server
-- Enabling guest account
GRANT CONNECT TO guest
GO
ALTER ROLE [db_owner] ADD MEMBER [guest]
GO
-- create test login
USE MASTER
GO
CREATE LOGIN [test] WITH PASSWORD=N'Complex@1212';
GO

When you explore the logins of the AdventureWorks database, you will not see ‘test’ as a user. Now let us login into SQL Server using ‘test’ user. When user will login using ‘test’ user he will be logged in as guest user in AdventureWorks database. Every user who login to AdventureWorks as guest user it will have admin access to the database.

This was the precise situation my friend had and he was not able to understand where things went wrong. As I said earlier, unless there is special case, guest user should be disabled.

Run following script to drop ‘test’ account which we created.

USE MASTER
GO
DROP LOGIN [test]
GO

Script to disable the guest account

REVOKE CONNECT FROM guest
GO

Well, share your stories in comments area. Additionally, have you ever prank your friend, if yes, I want to know. Additionally, for master, msdb and tempdb database guest user is required for SQL Server. If you enable guest database on modeldb, all the subsequent created database will have it enable, which is serious security issue.

NOTE: Do not try any of the script on production server or live environment. These scripts are for better understanding of the subject.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

22 thoughts on “SQL SERVER – Disable Guest Account – Serious Security Issue

  1. Hello Pinal

    wonderful post
    can you add bit more info like

    1. How i can see whether the guest user is enabled or disabled ? ( can be by UI or by commands )
    2. What is the default case in database , whether guest is enabled by default ?

    Manoj

    Like

    • @Manoj,

      1. You can check this information via GUI as well as from Command.
      GUI: When disabled, you will see a red down arrow on Guest account in SSMS.
      Command: sp_helprotect @username = ‘guest’ will give all permissions guest account has in the database, if it has a grant connect access or Revoke Connect access.

      2. Untill SQL Server 2000, this account is by default enabled. Starting SQL Server 2005, this account is by default disabled. But this is not true for all databases, there are two system databases (master and tempdb) in which this account is still Active by default, no matter which version of sql server you are using, This is required by SQL Server.

      ~ IM.

      Like

    • Satish,

      If you go to Datanase>Security->Users and see a user/login with a red arrow down, then is a disabled user/login, and a user/login inside a database with an red arrow down is the one that does not have CONNECT rights to the database.

      Cheers

      Like

  2. Pingback: SQL SERVER – Detecting guest User Permissions – guest User Access Status « SQL Server Journey with SQL Authority

  3. Pingback: SQL SERVER – guest User and MSDB Database – Enable guest User on MSDB Database « SQL Server Journey with SQL Authority

  4. Pingback: SQL SERVER – Identifying guest User using Policy Based Management « SQL Server Journey with SQL Authority

  5. Hi Pinal,
    Sorry to write you on this thread!! But I m feeling a big issue, when I run a Data Processing SP through SSMS it takes about a min. but the same SP when run through the Job, it takes around an hour.
    I googled but did not get any solution.. Plz help me..
    Thanks & Regards
    Govind

    Like

  6. Hi Pinal,

    The blog is really exciting…well after reading this I’ve tried at my personal laptop and evrything is working fine but now I cant revoke the access to guest with the command ”REVOKE CONNECT FROM GUEST”

    and it is throwing the error

    Msg 15182, Level 16, State 1, Line 1
    Cannot disable access to the guest user in master or tempdb.

    Like

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #017 | SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Difference Between Login and User – SQL in Sixty Seconds #070 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s