Click here to Skip to main content
15,867,834 members
Articles / Programming Languages / C++

SQL SERVER – Identifying guest User using Policy Based Management

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
27 Feb 2012CPOL4 min read 21.7K   2   1
Identifying guest User using Policy Based Management

If you are following my recent blog posts, you may have noticed that I’ve been writing a lot about Guest User in SQL Server. Here are all the blog posts which I have written on this subject:

One of the requests I received was whether we could create a policy that would prevent users unable guest user in user databases. Well, here is a quick tutorial to answer this. Let us see how quickly we can do it.

Requirements

  1. Check if the guest user is disabled in all the user-created databases.
  2. Exclude master, tempdb and msdb database for guest user validation.

We will create the following conditions based on the above two requirements:

  1. If the name of the user is ‘guest
  2. If the user has connect (@hasDBAccess) permission in the database
  3. Check in All user databases, except: master, tempDB and msdb

Once we create two conditions, we will create a policy which will validate the conditions.

Condition 1: Is the User Guest?

Expand the Database >> Management >> Policy Management >> Conditions

Image 1

Right click on the Conditions, and click on “New Condition…”. First, we will create a condition where we will validate if the user name is ‘guest’, and if it’s so, then we will further validate if it has DB access.

Image 2

Check the image for the necessary configuration for condition:
Facet: User
Expression:
@Name = ‘guest’

Condition 2: Does the User have DBAccess?

Expand the Database >> Management >> Policy Management >> Conditions

Right click on Conditions and click on “New Condition…”. Now we will validate if the user has DB access.

Image 3

Check the image for necessary configuration for condition:
Facet: User
Expression:
@hasDBAccess = False

Condition 3: Exclude Databases

Expand the Database >> Management >> Policy Management >> Conditions

Write click on Conditions and click on “New Condition…” Now we will create a condition where we will validate if database name is master, tempdb or msdb and if database name is any of them, we will not validate our first one condition with them.

Image 4

Check the image for necessary configuration for condition:
Facet: Database
Expression:
@Name != ‘msdb’
AND @Name != ‘tempdb’
AND @Name != ‘master’

The next step will be creating a policy which will enforce these conditions.

Creating a Policy

Right click on Policies and click “New Policy…”

Here, we justify what condition we want to validate against what the target is.

Image 5

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

Image 6

Now we have options for two evaluation modes:

  1. On Demand and
  2. On Schedule

We will select On Demand in this example; however, you can change the mode to On Schedule through the drop down menu, and select the interval of the evaluation of the policy.

Evaluate the Policies

We have selected OnDemand as our policy evaluation mode. We will now evaluate by means of executing Evaluate policy. Click on Evaluate and it will give the following result:

Image 7

The result demonstrates that one of the databases has a policy violation. Username guest is enabled in AdventureWorks database. You can disable the guest user by running the following code in AdventureWorks database.

Image 8

SQL
USE AdventureWorks;
REVOKE CONNECT FROM guest;

Once you run the above query, you can already evaluate the policy again. Notice that the policy violation is fixed now.

Image 9

You can change the method of the evaluation policy to On Schedule and validate policy on interval. You can check the history of the policy and detect the violation.

Image 10

Image 11

Quiz

I have created three conditions to check if the guest user has database access or not. Now I want to ask you: Is it possible to do the same with 2 conditions? If yes, HOW? If no, WHY NOT?

Reference: Pinal Dave (http://blog.sqlauthority.com)

Filed under: Best Practices, CodeProject, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology
Tagged: Policy Management Image 12 Image 13 Image 14 Image 15 Image 16 Image 17 Image 18 Image 19

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Founder http://blog.SQLAuthority.com
India India
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 2200 articles on the subject on his blog at http://blog.sqlauthority.com. Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of two SQL Server books - SQL Server Programming, SQL Wait Stats and SQL Server Interview Questions and Answers. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.

Comments and Discussions

 
GeneralMy vote of 5 Pin
member6027-Feb-12 21:51
member6027-Feb-12 21:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.