Always good to know your GUEST in SQL Server

Few days back came across an interesting issue, which was new for me. Application owner buzzed me and raised concerns about the SQL Server security setup.

App.Owner: We have some serious issue with our database security. I fear wrong people having elevated access to our data.
Me: Ok, Let me have quick look and get back to you.
App.Owner: Yes please. We need to fix this now.
Me: Did a quick check and everything seems to be fine. You’re ID with db_owner and other users with read access to the database.
App.Owner: I was also under the same impression. But today, I came to know there are other users who were also are also able to modify the data. That’s not at all good.
Me: hmm that’s strange. I will do a complete check to figure out the loophole.
App.Owner: Thanks. I will wait to hear from you.
Me: Okay. Ahh Found the suspect. All those privileges were getting transmitted from guest user. I could see someone had mistakenly provided extra permission to guest user, which is not at all required for this environment. Now things are back to normal. Please have a look from your end and let me know you if you have any questions
App.Owner: Great! Thanks for fixing this. Have a good day
Me: You too, have a good day!

In my case issue was with guest user permission. Let’s see the same in demo

-First we will create a new database named ‘TESTDB’. Then will check the status of guest account.

Note: If guest user is enabled in Model database, the same will get applied to newly created database

-Now we will create a SQL Login ‘UserA’, a new table ‘EPL’ and insert 3 records.

We have just created a SQL Login it’s not mapped to any of the database. At instance level also no permission is provided.

Now let’s try doing a simple select on table EPL using UserA.

As expected, ‘UserA’ is not able to fetch the data from TESTDB. Now let’s try enabling and providing read and write permission to ‘Guest’ user in TESTDB. You cannot enable/disable Guest user through GUI, it can be done only with T-SQL.

To Enable Guest Account:

USE [DATABASE_NAME]

GO

GRANT CONNECT TO GUEST

GO

To Disable Guest Account:

USE [DATABASE_NAME]

GO

REVOKE CONNECT TO GUEST

GO

Command completed successfully. Now let’s try running some commands.

So now UserA is able to read and insert records in TESTDB. The same task can be performed by all users associated with the instance. When Guest user is enabled, it will authorize access for logins which are not mapped to the database.

Things to know about Guest user

• It’s always recommended to disable guest user for user database, unless you have any specific/special requirement.
• Guest user cannot be dropped, but can be disabled/enabled.
• Guest user cannot be disabled for Master and Tempdb database.
• On msdb database guest user should not be disabled. If you have any plans to do so, please read this.

Do you use Guest user? On what scenarios you use them?

Please share your thoughts via comments below.

 

Cheers,

Naveen