If you are familiar with SQL Server, you may have come across the concept of the Guest User. In my previous blog posts, I have discussed various aspects of the Guest User in SQL Server. Today, I want to address a common request I received from readers – how to create a policy that prevents users from enabling the guest user in user databases. In this tutorial, I will show you how to achieve this in a few simple steps.

Requirements

Before we begin, let’s outline the requirements for our policy:

• Check if the guest user is disabled in all user-created databases
• Exclude the master, tempdb, and msdb databases from guest user validation

Creating Conditions

To create our policy, we need to define two conditions:

Condition 1: Is the User Guest?

Expand the Database >> Management >> Policy Management >> Conditions. Right-click on Conditions and click on “New Condition…”. In this condition, we will validate if the user name is ‘guest’ and if it has DB access.

Condition 2: Does the User have DBAccess?

Expand the Database >> Management >> Policy Management >> Conditions. Right-click on Conditions and click on “New Condition…”. In this condition, we will validate if the user has DB access.

Condition 3: Exclude Databases

Expand the Database >> Management >> Policy Management >> Conditions. Right-click on Conditions and click on “New Condition…”. In this condition, we will validate if the database name is master, tempdb, or msdb. If the database name is any of them, we will not validate our first condition with them.

Creating a Policy

Right-click on Policies and click “New Policy…”. Here, we specify the conditions we want to validate against the target.

• Condition: Has User DBAccess
• Target Database: Every Database except (master, tempdb, and MSDB)
• Target User: Every User in Target Database with the name ‘guest’

We have options for two evaluation modes: On Demand and On Schedule. In this example, we will select On Demand, but you can change the mode to On Schedule and select the interval of the policy evaluation.

Evaluating the Policies

Now that we have set up our policy, we can evaluate it by executing the “Evaluate policy” command. This will check if there are any policy violations.

If a violation is found, you can take appropriate action to fix it. For example, if the guest user is enabled in a database, you can disable it by running the following code:

USE AdventureWorks;
REVOKE CONNECT FROM guest;

Once you have fixed the violation, you can re-evaluate the policy to ensure compliance. You can also change the evaluation mode to On Schedule and validate the policy at regular intervals.

Quiz

Now, let’s test your understanding. I have created three conditions to check if the guest user has database access or not. Can you achieve the same result with just two conditions? Think about it and try to come up with an answer.

Answer: No, it is not possible to achieve the same result with just two conditions. The third condition, which excludes certain databases, is necessary to ensure that the first condition is not validated against those databases. Without the third condition, the policy would incorrectly flag violations in the excluded databases.

I hope this tutorial has helped you understand how to create a policy to prevent users from enabling the guest user in SQL Server. By implementing this policy, you can enhance the security of your databases and ensure that the guest user is not misused.