“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)
