SQL SERVER – Disable Guest Account – Serious Security Issue

guestbadge 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.”


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
-- Note: NEVER run this on production server
-- Enabling guest account
ALTER ROLE [db_owner] ADD MEMBER [guest]
-- create test login
CREATE LOGIN [test] WITH PASSWORD=N'Complex@1212';

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.


Script to disable the guest account


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)

29 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,

      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.


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



  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


  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.


    • Hi Pinal

      I have a set of read only databases as part of log_shipping. On a separate server on different domains to the source db’s ( Head office.) sql server 2008R2

      I would like to grant some special investigation teams at Head office access to these read only DB and don’t seem to be able to get this right without giving sysadmin rights to the users/group.

      Again, there are always special cases where there is a need of this guest account
      Would enabling the guest account on the server be a (special case) solution to my dilemma.


  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

  9. Hi Pinal,
    I had accessed my testDB in SQLServer 2012 about a year ago. Now even though It appears in the list after master, when I click on it, it gives me error. So even deleting and rerunning my scripts is out of question, as when I try to drop database I get execute permission denied. What could be the issue? Any help is appreciated.



  10. Hi Pinal,

    This is interesting and informative blog but I couldnt find the answer I was looking for. My question is same as Rajnish’s and Srinivasa’s which is yet to be answered. Can/Should we disable the Guest User from distribution database?


  11. Hi Pinal,

    Thanks for the detailed explanation of Guest User Account but I still couldnt find what I was looking for. My question is same as Rajnish’s and Srinivasa’s, can (should) we disable the Guest User on distribution database?


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