Tag Archives: SQL Server tutorial

User-Defined Server Roles in SQL Server 2012

A long awaited security feature in SQL Server 2012 has been user-defined server roles. SQL Server has long had flexible user-defined database roles for database-level permissions (which you’ll learn about later in this chapter), but with custom server roles you can finally get as granular with server-level permissions.

In old versions of SQL Server, the only way to grant some kinds of permissions to users was to assign them to a built-in fixed server role, which usually had way too many permissions. Making everyone a sysadmin was a horrible but common practice, particularly problematic because you can’t deny a sysadmin anything. This violates the principal of least privilege in a big way, but was often a practical necessity. SQL Server 2005 and later made all this more granular, letting you assign just about any specific server-level permission to a user, but lacked the ability to group those permissions into a server role.

SQL Server 2012 solves that problem with its support for user-defined server roles. Creating a new server role is as simple as using the CREATE SERVER ROLE statement:

Then you can grant and deny any server-level permissions you want. The following code grants the CONTROL SERVER permission to the new role— akin to granting sysadmin privileges—then denies a few permissions to narrow down the privileges of the members of the server role. This is a very flexible way to grant the users who are members of the group specific permissions.

To test the role, the code then creates a login associated with a Windows group, DBAs, on a machine named Willow, and adds the new login to the LimitedDBA role.

The code then creates a SQL Server login carol, with no permissions whatsoever within the instance of SQL Server. Then the code attempts various actions under carol’s security context that require server-level permissions: create another login, view system information, and create another server role. All of these actions fail, as you can see in the Figure below, because the carol principal has no permissions to perform these actions.

SQL-Server-2012-Failed-Actions

Next the code adds carol to the new LimitedDBA user-defined server role, and once again attempts to perform the same actions. As you can see in the Figure below, this time carol is able to get system information (the SELECT action), because that permission is granted through the CONTROL SERVER permission. But carol still can’t create logins or server roles, because those permissions were explicitly denied from the LimitedDBA role.

SQL-server-level-actions

In order to view all of the available server-level permissions that you can grant and deny to server roles, execute the following code. The Figure below shows the results.

SQL-partial-list-of-server-level-permissions

You can create user-defined server roles to grant users and groups a very specific set of permissions that they need to do their job, and no more. This is far more flexible than earlier versions of SQL Server, making security management far easier with SQL Server 2012, and easier management inevitably means a more secure server.

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

SQL Server 2012: Blocking and Deadlocks

When you modify data in SQL Server, it is important to understand how isolation levels and locking can affect performance and concurrency. The term concurrency refers to the ability of multiple users or applications to access the same data. When you update data, concurrency conflicts occur when multiple users select data and then try to update the data simultaneously. SQL Server places various kinds of locks on database objects and table rows in order to ensure data integrity.

One problem is that locks on the data can delay the updates. Another problem is that users may be able to update data that has changed since they last inspected the data. Applications often prevent this by adding a WHERE clause to the update to ensure that the update will succeed only if the data in the database still has the values that the user originally retrieved. The following article discusses Blocking and Deadlocks.

Blocking and Deadlocks

SQL Server is very efficient at applying and releasing locks. If you are modifying data using the default READ COMMITTED isolation level, locks will be applied and quickly released on each row of data. But anytime a resource is locked in SQL Server, it may cause other processes to be unable to access data necessary for the other processes to perform its actions. Two situations you have to be aware of and prepared for are blocking and deadlocks.

Blocking

Blocking occurs when locks are held for too long. When a transaction is blocked because another transaction has locks on data, it just sits there and waits. Over a network, blocked transactions will either hang or eventually time out with an error message.

You can reduce the chance of common blocking problems by avoiding user interaction in the middle of transactions, keeping data modifications short, and avoiding recursive operations and nested triggers.

Deadlocks

A deadlock occurs when two separate processes are each holding a resource that the other needs. Each process is waiting to release the resource it is holding until the other resource becomes available. Unless one of the processes is forced to yield, they will stay deadlocked forever. Deadlocks are difficult to simulate in a development environment; they seem to appear only when your database is running and many users attempt to complete the same operation at the same time.

The Figure below displays how a deadlock works. In this case, Transaction 1 has a lock on Resource 1 and needs a lock on Resource 2 to perform its work. Transaction 2 has a lock on Resource 2 and needs a lock on Resource 1 to perform its work. Transaction 1 can’t get a lock on Resource 2, and Transaction 2 can’t get a lock on Resource 1. So the transactions sit, deadlocked, waiting for the other transaction to let go of its locks.

Two transactions in a deadlockSQL Server uses an interval to determine which processes are running and which are blocked. If this interval passes twice and a process is still blocked, SQL Server chooses a deadlock victim. The victim’s transaction is rolled back, and error code 1205 is returned to the client application of the losing transaction. Your error handling routine in the client application can test for error 1205 and resubmit or cancel the query. If SQL Server did not select a deadlock victim, then eventually your server would run out of available processes and crash.

To avoid deadlocks, access objects in the same order every time, so that if a process is blocked it goes into a wait queue until the process that is holding the lock is complete.


Thumbnail for 566This post is an excerpt from the online courseware for our
SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

T-SQL Fundamentals: Outer Joins

One of the fundamental concepts of relational databases is the normalization of data into tables, or sets, of similar data. Different data elements are grouped into separate tables. Data about employees is in the dbo.Employees table, data about orders is in the dbo.Orders table, and so forth. The process of organizing the various elements of data into tables is called normalization. The key measure of successful normalization is the ability to join tables effectively.

As the SQL standard has evolved, so has the join notation, both inner and outer. The earliest join notation uses the WHERE clause to enforce the joining criteria. This article takes a deeper dive into outer joins.

Outer Joins

An outer join includes all of the rows from either or both of the tables in the join, even if there is a row in either table that doesn’t have a match in the other table. Three types of outer joins are possible: left, right, and full.

  • A left outer join includes all rows of the first table, even if there is no matching row in the second table.
  • A right outer join includes all rows of the second table, even if there is no matching row in the first table.
  • A full outer join includes both tables so that all rows of both tables are part of the result set even if there isn’t a match in the other table.

The “left” and “right” directions simply reference which table you list first and second in the join clause, respectively.

The challenge of using outer joins is to know when they are appropriate, and which one to use. The following query uses an inner join to generate a list of all customers and the date of each customer’s first order. We’ll use this query to show how the different outer joins work for this relationship between the Customers and Orders table. A second query returns the total number of Customers.

The query joins the Customers table to the Orders table and groups the results by company name. The MIN aggregate function returns the lowest order date for each customer. The first few rows of the result set shown in the Figure below display each customer and the date of their first order. The query returns 89 rows. You can see in the results for the second query that there are 91 customers in the table.

Using an inner join to return the first order of each customer

WARNING! Be careful with the number of rows reported by the query results window when executing multiple queries. When you first run those two SELECT queries, it reports 90 rows. But that is the total of all rows returned by the two queries, including the SELECT COUNT(*) query. To show the row count of 89 in the Figure above, we selected the grid for the first query’s results, so it displays the row count for just that one query.

Why is there a discrepancy between the number of customers in the table and the list of first orders? The list, generated using an INNER JOIN, doesn’t include every customer. The behavior of the inner join excludes any customers who have not placed any orders, which means that there isn’t a matching order record for two customers. If you need to see a list of all customers regardless of whether they’ve placed an order, as well as the date of the first order for customers who have, you need to use an outer join.


Thumbnail for 566This post is an excerpt from the online courseware for our
SQL Server 2012: T-SQL Select, Where, and Clauses course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

T-SQL: TOP Values

A general best practice of data selection queries is that you never want to return more data from the database server than you’re going to use. Even with a tightly focused WHERE clause, a query might still return more data than a user can make use of. You can further restrict the number of rows returned from a query using the TOP clause of a SELECT statement.

The TOP clause lets you limit the number of rows in a result set. For example, the following query selects the three cities with the highest number of employees, no matter how many cities might otherwise be returned from the query.

The Figure below shows the result set from the query. A key element is the ORDER BY clause, which sorts the result set in descending order before the TOP clause is applied. (A sort in ascending order would return the three cities with the fewest number of employees.)

Using TOP functions

However, one problem with a TOP query is the matter of ties. In the previous query, several cities have only one employee. If you want to see the ties for last place, you need to use the WITH TIES clause:

The Figure below shows the result set. The query returns all three cities that tied for last place. For the data in this table, only a TOP 2 query will return unique values.

TOP 3 WITH TIES

TOP also enables you to specify a percent value rather than an absolute number. Here’s an example of using TOP with PERCENT to return the top 25% records. The results are shown in the Figure below.

TOP 25 PERCENT

TIP: You can use any numeric expression, even a variable, to specify the number in a TOP clause. You can also use TOP in INSERT, UPDATE, and DELETE statements.

The GROUP BY clause in a SELECT statement is a powerful way to shape the data returned by a data selection query to perform basic data analysis. It might take some time to wrap your head around the set-based operation performed by non-trivial grouping, but it provides a very flexible way to wring information out of your data.


Thumbnail for 566This post is an excerpt from the online courseware for our
SQL Server 2012: T-SQL Select, Where, and Clauses course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.


SQL Server Management Studio: Displaying and Filtering Objects

sql production schema

SQL Server Management Studio displays server and database information in Object Explorer, which presents information about database objects in a treeview. In addition to displaying SQL Server Database Engine objects, Object Explorer also displays Analysis Services, Reporting Services, Integration Services, and other objects. You can also use it to administer SQL Server Security, SQL Server Agent, Replication, and Database Mail. It provides you with deep insight into just about every corner of an instance of SQL Server and all its services.

The Object Explorer displays information in a hierarchical treeview for all servers to which it connects. It populates the treeview on demand when you expand a node. You can double-click a node to expand it, and click the Stop button to stop the expansion.

Displaying and Filtering Objects

Object Explorer can display up to 65,536 objects. If you want to view additional objects, you need to close some nodes or apply a filter to reduce the number of displayed objects. To filter the view, select the parent node that you want to filter, then right-click and choose Filter|Filter Settings. For example, in the Tables node you can filter by Name, Schema, Owner, or Creation Date and you can choose which operator to use for each, as shown in the Figure below for the AdventureWorks2012 database. Click OK to display only tables in the Production schema.

When you filter a list of items, Object Explorer displays that information in the node name, as shown in the Figure below. This way, you don’t forget that it is filtered when you’re looking for an object that the filter excluded.

filtered view in object explorer

To remove the filter, right-click the node where you applied the filter and choose Filter|Remove Filter. Now all the database tables again appear in the Tables node.

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Configuring Management Studio course written by expert Don Kiely.