“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 (https://blog.sqlauthority.com)
24 Comments. Leave new
(last line) GRANT CONNECT to guest to disable the guest account? you mean, revoke from?
I think to disable guest account in sqlserver revoke is used.
REVOKE CONNECT FROM GUEST
I think you meant to write “REVOKE CONNECT FROM guest”.
Hi Pinal,
Well explained the scenario and small correction in “Script to disable the guest account”. Script should be like the following,
REVOKE CONNECT FROM GUEST
GO
Gopalakrishnan Arthanarisamy
Unisys, Bangalore.
I think Dave wants to make sure people are reading his blog… and staying alert :-)
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
@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.
Thank you Imrah
Manoj
Well, my pranks went as far as editing performance numbers in query output after enabling IO STATISTICS, anyway it made an impression.
Hi Pinal,
Nice one.
How to find whether Guest login is enabled or not?
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
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
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,
My bad I didn’t complete reading the article..now I got the answer. and anyways thanks for the interesting blog.
Hi Pinal,
Can we move forward to diasble the guest access from distribution database (Sysytem db)as this is violation of company standard.
Hi Pinal,
Could we disable the guest connect permission to distribution database (System database)?
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.
You need to give permission on primary database that would move to secondary automatically.
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.
Thanks
When you expand security / login.. how many accounts do you see? I feel that you are not sysadmin now.
Hi Pinal, What is impact of having CONNECT TO guest on a user database. want to understand the connect
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?
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?