Tag Archives: SQL Server 2012 tutorial

SQL 2012 Default Schemas for Users

2.0-bigsqllogoSQL Server doesn’t automatically create a schema with the same name as the user when you create a user. Instead you have to explicitly create a schema, assign ownership to a user, then create and add objects to that schema. You can (and usually should) assign a default schema to a user so that all objects the user creates—and doesn’t explicitly assign to another schema—become part of the default schema.

The following code shows how this works. After creating the DefaultSchema database and changing the database context to it, the code creates the login carol, maps it to the user carol in the database, and grants it the ability to create tables. It then changes the execution context to the new user carol.

 

The code next attempts to create a new table1. But when the code created carol it didn’t assign a default schema. SQL Server attempts to use the dbo schema, which is the default fallback schema. But Carol doesn’t have ownership rights in the database so she can’t create objects in the dbo schema.

Since carol doesn’t have the needed permissions the CREATE TABLE statement fails with this error message. In this case the problem of the two suggested is that carol doesn’t have permissions.

After reverting to the original admin login that started this session, the code creates a schema and gives ownership to user carol. You’ll see the AUTHORIZATION clause a lot in SQL Server because it lets you assign ownership in the same statement that creates or alters an object.

The code then once again changes the execution context to carol and attempts again to create table1. But, damn, it fails again! The problem now is that just because a user owns a schema doesn’t mean that it’s the user’s default schema. A user could own hundreds of schemas and SQL Server shouldn’t be responsible for picking one to be the default. But what does finally work is creating the table to be explicitly contained within the schema. The following statement explicitly creates table1 in the carolSchema, which finally works.

Success at last!!!

Thumbnail for 566

This 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 2012 Roles, Permissions, and Schemas

The relationship of roles, permissions, and schemas is an important security concept in SQL Server. A fully qualified database object name consists of four parts:

Usually you’ll just need to refer to objects in the current database context by using the schema and object name. A schema is a collection of objects, such as tables and code modules, as shown in the Figure below. This method simplifies user management, particularly when you have to change ownership of objects. But more importantly, it simplifies permissions management.

SQL-sample-schema

You can assign permissions on a schema that apply to all objects in the schema. For example, if you assign SELECT permission on CarolSchema to a principal, all three tables in that schema have that permission. Setting permissions individually on objects is always an option, but if you’ve designed the schemas in a database well, in some sort of functional categories that make sense for the database, you can set permissions on the schema and have them apply to dozens if not hundreds of objects. Best of all, the permissions you assign apply automatically to any future objects you add to the schema. Continuing the SELECT example, if a year from now you add Table4 to CarolSchema, all principals with SELECT permission on the schema automatically have that permission on the new table. Multiple users and roles can have the same default schema, and if a principal has no default schema set, SQL Server attempts to find the object in the dbo schema.


 Thumbnail for 637

 

 

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.

Contained Databases in SQL Server 2012

Contained databases is an interesting new part of SQL Server 2012. This isn’t directly a security feature as such, but it implements a new authentication feature and so has a security element. This chapter will not cover contained databases in detail, but briefly contained databases solves the problem of moving databases from one server to another. In the past, you had to move the database itself, in addition to server-level objects such as logins and SQL Agent job information. Getting everything configured on the destination server was a royal pain, particularly since you had to recreate server logins and remap security IDs (SIDs). Contained databases attempts to solve most of these problems.

The new authentication feature necessitated by contained databases is the ability to create a SQL user in a database with a password, or create a user associated with a Windows user without requiring an associated login.

Authentication takes place directly against the database, and a successful authentication results in a token that grants access to that database only. This serves to provide a tightly scoped and narrow security boundary around the database so that the authenticated user can only perform database-level operations.

The sample code demonstrates how contained databases works. You’ll need a contained database to try it out on, so there is also a PubsContained.sql script file that creates a contained version of the old pubs sample database. The main change, besides cleaning up some archaic statements, is how the code creates the PubsContained database:

The CONTAINMENT option set to PARTIAL results in a contained database, while a setting of NONE creates a regular database, which is the default. Using this clause creates the database and configures it to allow authentication against the database.

Before you run the script in PubsContained.sql, you have to enable the contained databases feature in this instance of SQL Server. A fresh installation of SQL Server minimizes the available attack surface for security vulnerabilities by installing some features but disabling them. Contained databases is one such feature.

The Contained Databases.sql file has the code to enable contained databases in an instance of SQL Server (it’s disabled by default). When making certain configuration changes in SQL Server, you have to enable the “show advanced” feature to allow the change, so this code sets that feature on, makes the change, and sets it back off. The relevant line of code for contained databases is the highlighted statement.

Now you can successfully execute the code in PubsContained.sql.

The code then creates a user floyd in the PubsContained database with a strong password, using the following statement. This part is what’s new in contained databases in SQL Server 2012: when a user attempts to log in as floyd, the authentication happens in the database, not at the server level.

Then you can start a new instance of Management Studio and log in as floyd (copy the password!). The log in fails, because the user floyd is not a server login and so cannot be authenticated using a traditional SQL login. In order to authenticate floyd in the PubsContained database, you need to click the Options button in the Connect to Server dialog box and set the Connect to database option to PubsContained, as you can see in the Figure below.

TIP: Don’t even try to select the database from the drop-down list; you’ve entered floyd’s login information, which doesn’t have the ability to even see which databases are available in the SQL Server instance. Your only option is to type in the database name.

SQL-Server-Entering-contained-database-to-connect-toWhen you click the Connect button, this time floyd is able to connect. At this point the user has access to the PubsContained database, but no permissions on any objects. And, as you can see in the Figure below, floyd has no access to any other server objects, so the only thing visible in Object Explorer is the PubsContained database. floyd is a very restricted user!

SWL-Server-Objects-visible-to-floyd-in-Object-Explorer

Thumbnail for 628

 

 

learnnowonline expert instructor don kielyThis 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: Creating a New Table with SELECT INTO in T-SQL

2.0-bigsqllogo

The SQL standard draws a distinction between Data Manipulation Language (DML) and Data Definition Language (DDL). DML statements manipulate and maintain data, while DDL statements change the structure and properties of database objects. SELECT statements are considered to be part of DML, even though you might not think of selecting data as a form of “manipulation.”

When you retrieve data with a SELECT statement, SQL returns a result set. However, any actions you take on that data (besides just looking at it) involve data manipulation queries that use one of the following three T-SQL statements: INSERT, UPDATE, or DELETE. SQL queries that use these statements are sometimes known as action queries.

SQL Server was designed to handle data modification extremely efficiently via T-SQL DML statements. This article takes a look at the SELECT INTO statement for creating new tables using T-SQL.

SELECT INTO

The SELECT INTO statement creates a new table with the columns necessary to contain all the values that the SELECT statement returns. The following query creates a new table called Produce, which contains all of the rows that match the produce category in the Products table:

If you run the statement again, you’ll receive an error that the Produce table already exists.

NOTE: When you create a new table with SELECT INTO, the new table schema will match the original schema, including identity columns. The Produce table will have a ProductID that is an identity column. The nullability of columns is also copied from the source, but indexes, constraints, and triggers are not copied.

Temporary Tables

Although the SELECT INTO syntax allows you to create tables in a database, you probably won’t want to use this technique very often in applications. When you allow users to create tables at will, problems can occur with backups, security, and database clutter. But sometimes you’ll want to create temporary, or temp, tables as a way to store data that’s necessary for processing but is not required afterward.

You can create temp tables so that only the connection that creates them can access them. Once that connection ends, SQL Server automatically destroys the tables, which eliminates all of the problems with persistent tables. Temp tables are stored in the tempdb database, and are identifiable by the first character of their name: the pound symbol (#).

NOTE: Temp tables created in stored procedures are destroyed when the stored procedure terminates.

The following SELECT INTO query creates a temporary table named #Produce:

Namespace conflicts won’t occur if multiple users create a #Produce table, since every connection can have its own #Produce in tempdb. Each #Produce table in tempdb has a suffix with an underscore and the connection information to keep them separate. To select data from the temp table, use the following query:

Another way to create temp tables is to use the standard CREATE TABLE syntax, as shown in the following DDL statement:

The following query inserts data into the temp table. Note that the data types you explicitly define in the temp table must match the data types in the table you are selecting data from:

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

The Sky’s the Limit with the T-SQL WHERE Clause

You will rarely want SQL Server to return every row in a table. The most efficient queries retrieve only the data you will actually use—no more, no less. To limit the rows that the query returns, you have to specify the subset of records that you want. The WHERE clause is the primary row filter of a SELECT statement.

You use the WHERE clause to specify the search conditions that SQL Server should use to identify rows that should or shouldn’t be included in the result set. The simplest WHERE clauses check for equality, so that each row returned has to meet the specified condition.

Using Transact-SQL comparison operators in a WHERE clause lets you build queries that search for records other than those that are exactly equal to a static value. This article takes a look at using the IN comparison operator.

Using the IN Operator

Sometimes you have to filter rows of a table based on whether a column’s value matches multiple values. You can use the OR operator to filter on a list of values, as shown in the following query, which returns customers in France or Spain:

However, a much more efficient way (one that SQL Server can often execute faster) is to use the IN operator. The IN operator compares a field against an array of values. The following query returns the same list of all customers in France and Spain:

The following Figure shows the result set:

Using the IN operator

TIP: You can also use the IN operator with a subquery, which is a query within a query. For example, to find all customers who have not placed orders, you could use this:

SQL Server first executes the subquery (highlighted in this code), then uses that list of CustomerID values as the set of values used by the IN operator to compare CustomerID values in the Customers table against. So you are not limited to using the IN operator with a fixed set of static string values.

The WHERE clause of a SELECT statement in SQL Server is an incredibly powerful way to filter the rows returned by a data selection query. There are a number of ways to put this clause to use, and as you learn more about T-SQL you’ll see that you can get very creative with how you control the rows in a query result set. By creating a filtering condition based on other data in the database, you can dynamically return results based on the current state of your data, rather than a fixed set of static conditions. The sky’s the limit!


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: Using the SELECT Statement for Naming Columns

Giving a name to the computed column

One of the most versatile statements in all of T-SQL is the SELECT statement. It’s guaranteed to become your go-to tool for retrieving data stored in a SQL Server database, with a dizzying array of clauses and options that let you select data from any databases and tables, shape it however you want, and apply conditions and filters so that you get exactly the data you need. This article discusses how to use the SELECT statement for naming columns.

Naming Columns

Notice in the Figure below that the column has no name. Since the column is the result of an expression, rather than a column in the database, you must explicitly name the column if you want to be able to reference the calculated column in a client application. Here is how you can do that by using the AS keyword:

The query itself is essentially the same as the previous example; all that’s changed is that the column now has a name, FullName. This name is also called an alias. Aliases are used routinely in complex SQL and are necessary for certain types of joins, such as the self joins that you’ll learn about later in the chapter. To include a space within an alias, surround the alias with square brackets; the results appear in Figure 4.

 

The AS clause is optional—you can name a column just by using the name separated from the column list by a space:

 

This query functions in the same way as the query with the AS clause, but makes your SQL statement harder to read. Is FullName a column in the database and the programmer forgot the comma, or an alias? It is better to be explicit with the AS clause. The AS clause is the most explicit way to alias a column and is supported by the ANSI standard.

Another supported option is to use an equal sign for defining a column alias, which produces the same results as the previous statements. This syntax is required in some cases when using more advanced features of the SELECT statement.

Deprecated Syntax – One method of defining column aliases using the equal sign (=) is now deprecated, which means it will not be supported in future versions of SQL Server. (Even though it has been deprecated for a long time, it still works in SQL Server 2012.) This method uses a string expression to define the alias:

You can, however, still use a string expression to define the column alias if you use the AS syntax (even if you leave out the word AS).


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: DATEADD and DATEDIFF Functions

Date and time functions perform operations on date and time values to return a string, numeric, or date/time value. Compared to the string manipulation functions in T-SQL, you have way more flexibility when working with dates and times.

Use DATEADD to perform computations on dates by adding an interval of time to the specified date. The following query adds 2 to the year, month, and day. The Figure below shows the results.

Performing computations on dates with DATEADDTo subtract from a date, use DATEADD and pass in a negative number for the time interval.

Use DATEDIFF if you want to calculate the length of time between two date/time values. As with DATEADD, DATEDIFF allows you to specify the unit of measure. The following query uses the Northwind database to calculate the number of days that elapsed between various dates in the Northwind Orders table, to provide various metrics about company performance. The first date passed to DATEDIFF is subtracted from the second date. The Figure below shows a few rows of the result set.

DATEDIFFThe following code uses a query that specifies a date and then calculates the last day of the month that the date is in. The logic is to add one month to the date and then subtract the number of days in the day portion of that date in the next month. This brings the calculation back to the last day of the original date’s month. This calculates that 2016-02-29 is the last day of that month.

In SQL Server 2012, an even easier way to find the last day of the month is with the EOMONTH function, which takes a date and an optional parameter that lets you specify a number of months to add. The following code finds the last day of the month for the same date as the previous code, as well as the last day of the month five months later. Notice that in February the last day is the 29th but in July it is the 31st, as shown in the Figure below.

EOMONTH


ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL
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.