SQL SERVER – 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

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.

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.

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 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.

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.

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

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:

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.

USE AdventureWorks;
REVOKE CONNECT FROM guest;

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

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.

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)

About these ads

SQL SERVER – Merry Christmas and Happy Holidays – Database Properties – Number of Users

First of all Merry Christmas and Happy Holidays to everybody. I wish you best holiday season.

In today’s blog post – I am sharing very small question received by one of the reader. Though simple sometime a small question make people think. He sent me very similar to following image and asked few questions. As his image represented his server’s information, I am reproducing very similar image using AdventureWorks database.

Question: What does the number of users signifies in database properties? Does this mean current connected users or total active users or total enabled users or what exactly?”

Answer: Database Properties >> Number of Users indicates the how many users exists in the database.

In my current example you can see that there are 5 users and that is displayed in the above properties screen. Though, it is very simple question, now I am going to ask you question back.

Question to readers: If you notice there is small arrow besides GUEST or SYS login. It is RED arrow on south. What does this arrow means?

Please leave your answer in comments area.

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

SQL SERVER – Bad Practice of Using Keywords as an Object Name – Avoid Using Keywords as an Object

Madhivanan is SQL Server MVP and very talented SQL expert. Here is one of the nugget he shared on Just Learned.

He shared a tip where there were two interesting point to learn.

  1. Do not use keywords as an object name
  2. [read DHall's excellent comment below]

He has given excellent example how GO can be executed as stored procedure. Here is the extension of the tip. Create a small table and now just hit EXEC GO; and you will notice that there is row in the table.

Create Stored Procedure
CREATE PROCEDURE GO
AS
SELECT
1 AS NUMBER

Create Table
CREATE TABLE T1 (ID INT)

Now execute following code
INSERT INTO T1(ID)
EXEC GO;

Now when selecting from table it will give us following result:
SELECT *
FROM T1

Now see following resultset:

So without inserting any data we inserted the data, well indeed good puzzle but very bad practical practice. Every body should be aware of this gotcha and avoid it. Thanks Madhivanan for teaching this interesting learning.

Republishing here with authorization of Just Learned.

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

SQL SERVER – Using expressor Composite Types to Enforce Business Rules

One of the features that distinguish the expressor Data Integration Platform from other products in the data integration space is its concept of composite types, which provide an effective and easily reusable way to clearly define the structure and characteristics of data within your application.  An important feature of the composite type approach is that it allows you to easily adjust the content of a record to its ultimate purpose.  For example, a record used to update a row in a database table is easily defined to include only the minimum set of columns, that is, a value for the key column and values for only those columns that need to be updated.

Much like a class in higher level programming languages, you can also use the composite type as a way to enforce business rules onto your data by encapsulating a datum’s name, data type, and constraints (for example, maximum, minimum, or acceptable values) as a single entity, which ensures that your data can not assume an invalid value.  To what extent you use this functionality is a decision you make when designing your application; the expressor design paradigm does not force this approach on you.

Let’s take a look at how these features are used.  Suppose you want to create a group of applications that maintain the employee table in your human resources database.

Your table might have a structure similar to the HumanResources.Employee table in the AdventureWorks database.  This table includes two columns, EmployeID and rowguid, that are maintained by the relational database management system; you cannot provide values for these columns when inserting new rows into the table.

Additionally, there are columns such as VacationHours and SickLeaveHours that you might choose to update for all employees on a monthly basis, which justifies creation of a dedicated application.

By creating distinct composite types for the read, insert and update operations against this table, you can more easily manage this table’s content.

When developing this application within expressor Studio, your first task is to create a schema artifact for the database table.  This process is completely driven by a wizard, only requiring that you select the desired database schema and table.  The resulting schema artifact defines the mapping of result set records to a record within the expressor data integration application.  The structure of the record within the expressor application is a composite type that is given the default name CompositeType1.  As you can see in the following figure, all columns from the table are included in the result set and mapped to an identically named attribute in the default composite type.

If you are developing an application that needs to read this table, perhaps to prepare a year-end report of employees by department, you would probably not be interested in the data in the rowguid and ModifiedDate columns.  A typical approach would be to drop this unwanted data in a downstream operator.  But using an alternative composite type provides a better approach in which the unwanted data never enters your application.

While working in expressor  Studio’s schema editor, simply create a second composite type within the same schema artifact, which you could name ReadTable, and remove the attributes corresponding to the unwanted columns.

The value of an alternative composite type is even more apparent when you want to insert into or update the table.  In the composite type used to insert rows, remove the attributes corresponding to the EmployeeID primary key and rowguid uniqueidentifier columns since these values are provided by the relational database management system.

And to update just the VacationHours and SickLeaveHours columns, use a composite type that includes only the attributes corresponding to the EmployeeID, VacationHours, SickLeaveHours and ModifiedDate columns.

By specifying this schema artifact and composite type in a Write Table operator, your upstream application need only deal with the four required attributes and there is no risk of unintentionally overwriting a value in a column that does not need to be updated.

Now, what about the option to use the composite type to enforce business rules?  If you review the composition of the default composite type CompositeType1, you will note that the constraints defined for many of the attributes mirror the table column specifications.  For example, the maximum number of characters in the NationaIDNumber, LoginID and Title attributes is equivalent to the maximum width of the target column, and the size of the MaritalStatus and Gender attributes is limited to a single character as required by the table column definition.  If your application code leads to a violation of these constraints, an error will be raised.  The expressor design paradigm then allows you to handle the error in a way suitable for your application.  For example, a string value could be truncated or a numeric value could be rounded.

Moreover, you have the option of specifying additional constraints that support business rules unrelated to the table definition.

Let’s assume that the only acceptable values for marital status are S, M, and D.  Within the schema editor, double-click on the MaritalStatus attribute to open the Edit Attribute window.  Then click the Allowed Values checkbox and enter the acceptable values into the Constraint Value text box.

The schema editor is updated accordingly.

There is one more option that the expressor semantic type paradigm supports.  Since the MaritalStatus attribute now clearly specifies how this type of information should be represented (a single character limited to S, M or D), you can convert this attribute definition into a shared type, which will allow you to quickly incorporate this definition into another composite type or into the description of an output record from a transform operator.

Again, double-click on the MaritalStatus attribute and in the Edit Attribute window, click Convert, which opens the Share Local Semantic Type window that you use to name this shared type.  There’s no requirement that you give the shared type the same name as the attribute from which it was derived.  You should supply a name that makes it obvious what the shared type represents.

In this posting, I’ve overviewed the expressor semantic type paradigm and shown how it can be used to make your application development process more productive.  The beauty of this feature is that you choose when and to what extent you utilize the functionality, but I’m certain that if you opt to follow this approach your efforts will become more efficient and your work will progress more quickly.  As always, I encourage you to download and evaluate expressor Studio for your current and future data integration needs.

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

SQL SERVER – 2012 – Summary of All the Analytic Functions – MSDN and SQLAuthority

SQL Server 2012 (RC0 Available here) has introduced new analytic functions. These functions were long awaited and I am glad that they are now here. Before when any of this function was needed, people used to write long T-SQL code to simulate these functions. But now there’s no need of doing so. Having available native function also helps performance as well readability.

In the last few days I have written many articles on this subject on my blog. The goal was to make these complex analytic functions easy to understand and make them widely accepted. As these new functions are available and as awareness spreads about them, we should start using these new functions. Here is a quick list of the new functions and relevant MSDN sites:

Function SQLAuthority MSDN
CUME_DIST CUME_DIST CUME_DIST
FIRST_VALUE FIRST_VALUE FIRST_VALUE
LAST_VALUE LAST_VALUE LAST_VALUE
LEAD LEAD LEAD
LAG LAG LAG
PERCENTILE_CONT PERCENTILE_CONT PERCENTILE_CONT
PERCENTILE_DISC PERCENTILE_DISC PERCENTILE_DISC
PERCENT_RANK PERCENT_RANK PERCENT_RANK

I also enjoyed three different puzzles during the course of this series which gave a clear idea to the SQL Server 2012 analytic functions.

This series will always be my dear series as during this series I had went through a very unique experience of my book going out of stock and becoming available after 48 hours.

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

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Easy Introduction to CHECK Options – Day 24 of 35

Answer simple quiz at the end of the blog post and -

Every day one winner from India will get Joes 2 Pros Volume 4.

Every day one winner from United States will get Joes 2 Pros Volume 4.

Using Check Option

CHECK OPTION is a very handy tool we can use with our views. If I give you the definition right away and you don’t already know what it does then is just confusing. However the examples make perfect sense. So let’s save the definition for the end of this post. First let’s look at the creation of the vHighValueGrants view.

CREATE VIEW vHighValueGrants
AS
SELECT
GrantName, EmpID, Amount
FROM [Grant]
WHERE Amount > 20000

JProCo already offered to do a $1,000 match for each grant larger than $20,000.  The $1,000 match is currently included in our vHighValueGrants amounts as seen in the figure below.

We have just been informed that the campaign didn’t receive the necessary approval from the stakeholders. Therefore, we must remove all of the $1,000 matching amounts. After we run this UPDATE statement, the grants will all be returned to their baseline values.

UPDATE vHighValueGrants
SET Amount = Amount - 1000

The large grants are decremented by $1,000 and thus returned to their baseline values. The $1000 increase has been removed as seen in this figure below.

Suppose you accidentally ran the decrement step twice.  Before we “accidentally” run the UPDATE statement again in order to create this scenario, let’s consider the amounts currently shown by the view. The smallest grant in the vHighValueGrants view is $21,000.  If we rerun the UPDATE statement, this grant will become $20,000. Recall that each grant must be greater than $20,000 in order to appear in the view.

Run the UPDATE statement again and then run a SELECT statement to see all of the records in the vHighValueGrants view.  The $21,000 grant (contributed by Big 6’s Foundation %) was reduced to $20,000 and thus has fallen out of the view.  Now that this grant has fallen outside the criteria of vHighValueGrants, the view no longer has the ability to “see” or manipulate this record using DML statements.  For the five remaining grants, you can correct their amounts and reverse the “accidental” run of the UPDATE statement by incrementing each grant by $1000.  However, the only way to correct the amount of the missing grant is by running an UPDATE statement directly against the Grant table. There are only 5 large grants remaining in the view after the second $1000 decrement.

This action was clearly a mistake.  We didn’t intend to remove a record from the view, but as it is currently configured, vHighValueGrants isn’t protected against these kinds of mistakes.  In order to prevent data updates which would cause records to disappear from our view, we can either place a trigger on the Grant table, or we can use CHECK OPTION.

CREATE TRIGGER trg_UpdateGrant
ON dbo.[Grant]
AFTER
UPDATE
AS
BEGIN
IF
EXISTS( SELECT * FROM Inserted ins
INNER JOIN Deleted del
ON ins.GrantName = del.GrantName
WHERE del.Amount > 20000
AND ins.Amount <= 20000)
ROLLBACK TRAN
END

If you create the trigger and then attempt to decrement the vHighValueGrants view, you’ll find that the trigger will not allow the transaction to fall to $20,000 and thus it won’t meet the criteria of the vHighValueGrants view.

The trigger has protected our view.  The transaction which attempted to reduce a large grant from $21,000 to $20,000 was forbidden and ended in the trigger.

But let’s recognize that the trigger would also prevent any existing grant from ever being changed to an amount $20,000, or lower.  In other words, the trigger is so restrictive that even a DBA would be disallowed from directly updating the Grant table if the change would reduce an existing grant amount to become $20,000 or lower. The trigger is more restrictive than we intended.

Our goal was simply to restrict users from making an accidental data change through the view which would result in a grant being removed from the view. Let’s reattempt our goal by using CHECK OPTION by rebuilding the vHighValueGrants view to include CHECK OPTION.

This tells the view to disallow data changes through the view which would cause any record to fall outside of the criteria of the view. Does it work? Now attempt to decrement though the view and the CHECK OPTION will block you. Using the code below you get the following error message.

UPDATE vHighValueGrants
SET Amount = Amount – 1000

Msg 550, Level 16, State 1, Line 1

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

You will however be allowed to update to the table directly.

UPDATE [Grants]
SET Amount = Amount – 1000

OK as promised here is the short definition or description of what CHECK OPTION does for views. Each time a DML statement is run against the view, CHECK OPTION validates that the resulting record set will be true to the SELECT statement which built the view. If a modification would remove a record defined by the view, then CHECK OPTION prevents the transaction from being committed.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter5.1Setup.sql script from Volume 4.

Question 23

You have a table named dbo.Sales. You need to create three views from the sales table.

vSalesSeattle

vSalesBoston

vSalesSpokane

Each view will be used by each region to make changes to their rows.  One day a Seattle sales manager updated his sales data to have a new LocationID and the record showed up on the vSalesBoston view. Changes made to the vSalesSeattle view must not be made in a way that the record falls outside of the scope of the view.  Which view should you create for Region1?

  1. CREATE VIEW dbo.vSalesSeattle
    AS
    SELECT SalesID, OrderQty, SalespersonID, RegionID
    FROM dbo.Sales
    WHERE RegionID = 1
    WITH DIFFERENTIAL
  2. CREATE VIEW dbo.vSalesSeattle
    AS
    SELECT SalesID, OrderQty, SalespersonID, RegionID
    FROM dbo.Sales
    WHERE RegionID = 1
    WITH CHECK OPTION
  3. CREATE VIEW dbo.vSalesSeattle
    WITH SCHEMABINDING
    AS
    SELECT SalesID,OrderQty,SalespersonID, RegionID
    FROM dbo.Sales
    WHERE RegionID = 1
  4. CREATE VIEW dbo.vSalesSeattle
    WITH NOCHECK
    AS
    SELECT SalesID, OrderQty, SalespersonID, RegionID
    FROM dbo.Sales
    WHERE RegionID = 1

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

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

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – System and Time Data Types – Day 16 of 35

Answer simple quiz at the end of the blog post and -
Every day one winner from India will get Joes 2 Pros Volume 3.
Every day one winner from United States will get Joes 2 Pros Volume 3.

System and Time Data Types

Keeping track of date and time data points has always been a critical part of online transactional databases. For example, each sales invoice record needs a date-time stamp, as do systems which track quotes and customer contacts regarding sales opportunities.

Think of how many times during your workday that you rely on a date-time stamp as helpful metadata to sort or locate the latest information in a report or data source. Global organizations, in particular, have a need for their in-house communication, reporting, and collaboration tools to appropriately convey accurate date and time information in order to keep every part of the organization in sync.

Recap of DateTime Functions

GETDATE( ) and SELECT SYSDATETIME( ) both return the current date and time in your time zone. However, GETDATE( ) shows fractional seconds expressed in milliseconds (.333 second), and SYSDATETIME( ) shows fractional seconds expressed in nanoseconds (.3333333 second). SYSDATETIME( ) return similar results but their precisions differ.

What time is it right now in the UK? UTC is Coordinated Universal Time, formerly known as Greenwich Mean Time (GMT). (UTC is also known by the terms zulu time, world time, and universal time.)

SELECT GETUTCDATE( ) will show the current time expressed in terms of UTC. GETUTCDATE( ) is less precise than SYSUTCDATETIME ( ). However there is a UTC function that gets down to the nanoseconds, SYSUTCDATETIME( ). When we run all 4 of these functions together We see the two top times in my local time zone (in my case the Pacific time zone) and the two bottom times in UTC.

Question 16

Which one of the following functions will return the date and time in the current time zone to a precision of milliseconds?

  1. GETDATE( )
  2. SYSDATETIME( )
  3. GETUTCDATE( )
  4. SYSUTCDATETIME( )

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 3.
Winner from India will get Joes 2 Pros Volume 3.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

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