Tag Archives: SQL tutorial

Advanced Query Techniques

Thumbnail for 578

The T-SQL INSERT, UPDATE, and DELETE statements provide a lot of power and flexibility for maintaining data in a relational database. But they can also be unwieldy. Say that you have a table with rows that you need to use to update another table. Depending on the data in the source table, you might have to update or delete an existing row or, if no related row exists, insert it into the table. This can be unwieldy, requiring several statements to perform the various actions. And it can be a pain to get all the comparisons working just right.

The MERGE statement addresses this issue. It lets you insert, update, and delete table data with just a single statement, based on the similarities and differences in the data. It joins a data source table with a target table or view to perform the various actions. The operation it performs depends on the results of the join:

  • If data exists in both the source and the target, update the changed data in the target.
  • If data in the source doesn’t exist in the target, insert data into the target.
  • If data exists in the target but not in the source, delete the data from the target.

You have full control over how you define whether and how each of these actions will occur for a particular row in the source table. The MERGE statement is transactional, so you don’t need to explicitly define a transaction.
If any of the operations fails, the entire statement rolls back.

  • The MERGE statement uses five clauses:
  • The MERGE clause specifies the target data, such as a table, view, or other query.
  • The USING clause defines the source data.
  • The ON clause specifies how to join the tables, much like a regular JOIN clause.
  • One or more WHEN clauses define the actions to take based on the join matching.
  • The OUTPUT clause returns a row for each action taken.

The WHEN clause is where all the actions occur, and you’re likely to have more than one in a typical MERGE statement. The WHEN clause has three forms:

  •  WHEN MATCHED [AND condition]

Here is where you either update or delete an existing row in the target
data. You can have at most two of these clauses, and if you have two,
one must have a condition associated with it. You can define either an
UPDATE or DELETE statement in this clause. If you’re using an
UPDATE statement and more than one row in the target matches the
statement, you get an error, and you can’t update any row in the target
more than once. Similarly, you can’t update and delete the same row.


This is where you can use an INSERT statement, when a row in the source doesn’t match any row in the target. You can have only one of this WHEN form in any MERGE statement. The BY TARGET clause is optional, because it is the default.


This form of the WHEN clause kicks in when rows in the target don’t match a row in the source; in this case you can either update or delete the row in the target. You can have at most two of these in a MERGE statement, and the second must have a condition associated with it. 

The optional condition on some of the versions of the WHEN clause can be  just about anything you can include in a WHERE clause. You have to include  at least one WHEN clause in the MERGE statement, although the order you include the different forms doesn’t matter at all. What does matter is that if you include the same form with and without a condition, the clause with the condition must be first.

Any triggers that exist on the affected target table will fire normally, but there is no guarantee of the order in which triggers fire if multiple operations are performed on the target table. So be careful of side effects. All normal permissions apply, so the user or security context must be able to perform the various actions.
ldn-expertdkielyThis post is an excerpt from the online courseware for our Microsoft SQL Server 2012 Developer 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.

LINQ and Relational Data

linq-logoAt its most basic level, LINQ provides the ability to query any data source that supports the IEnumerable or generic IEnumerable(T) interface. The data you want to query in an application can come from a variety of sources. The data
may reside in in-memory objects. If so, you can use LINQ to Objects. The data may reside in XML. If so, you can use LINQ to XML.

The primary source of data in most applications is a relational database, such as SQL Server. If you have built database applications with Visual Studio, you are familiar with using ADO.NET and building SQL statements to query and modify data in a relational database.

In this chapter, you will see how to use LINQ to SQL to query and modify data in a SQL Server database. You may find yourself thinking that ADO.NET works great, so why do I need another way to work with relational data? What advantage does LINQ to SQL offer me?

LINQ provides a consistent model for querying all types of data. With LINQ, a query that works with objects looks similar to a query that works with XML. It also looks similar to a query that works with relational data. If you know how to use LINQ to Objects to write a query, you already know most of what you need to write a query by using LINQ to SQL.

LINQ to SQL is part of the ADO.NET family of technologies. It abstracts away the various ADO.NET classes such as SqlConnection, SqlCommand, SqlDataAdapter, SqlDataReader, etc. You get the same functionality by writing less code. You can also easily mix LINQ to SQL with existing ADO.NET code.

LINQ to SQL also bridges the gap between object programming and traditional database programming. As a .NET developer, you build applications based on objects. The .NET Framework consists of classes. To use the functionality that a class provides, you create an instance of the class and then use its properties, methods, and events.

To query a database, you build a SQL statement as a string. You can then use ADO.NET classes to send the query to the database. You can also store the results by using the ADO.NET DataSet and DataTable classes. Is this object programming? Yes and no. The use of ADO.NET is object programming; however, the data model is based on tables and rows, not on objects.

To model the data as objects, you can create classes for each table in the database. For example, you could create a Customer class with properties for company name, address, city, region, and so on. When you query the Customer table, you store the results in one or more instances of the Customer class.

LINQ to SQL provides a runtime infrastructure for managing relational data as objects. To use LINQ to SQL, you map the structure of a relational database to an object model. Each table in the database maps to a class. This class is an entity class. At runtime, the .NET Framework translates LINQ queries into SQL and sends them to the database. When the database returns results, the runtime populates the objects with the returned data.

Once you create your object model, you can use LINQ to SQL to query and change data in a SQL Server database.

Note: Microsoft built LINQ to SQL to support any relational database.
However, the implementation of LINQ to SQL that ships with the
.NET Framework 3.5 and 4.0 support only SQL Server. It is
possible that Microsoft will support other databases in future


ldn-expertkgetzThis post is an excerpt from the online courseware for ourWindows 8 Using XAML: Views, Resources, and Toastscourse written by expert Ken Getz.

Ken Getz is a Visual Studio expert with over 25 years of experience as a successful developer and consultant. He is a nationally recognized author and speaker, as well as a featured instructor for LearnNowOnline.

SQL Security Fundamentals: Changing the Execution



You don’t always want the caller’s permissions to be used to validate permissions in a broken ownership chain. Sometimes you want to execute the code as though it was being executed by another user entirely, by using that other user’s permissions to validate permissions on all of the accessed objects.

This is called switching the execution context of the code. This lets you take advantage of SQL Server’s granular permissions, keeping tight control over the permissions to underlying objects, while giving various users the ability to execute the code.

In SQL Server, when you define any kind of user-defined functions (except inline table-valued functions), stored procedures, and data manipulation triggers, you can use the EXECUTE AS clause as part of the definition of the code to run the code under the security context of the specified user.

Four EXECUTE AS options are available:

  • EXECUTE AS CALLER: The default for backward compatibility. The code executes in the context of the caller of the code, who must have permissions both to execute the code and to access underlying objects. The actual behavior depends on whether the ownership chain is broken or unbroken.
  • EXECUTE AS= ‘username’ and EXECUTE AS = ‘loginname’: The code executes in the context of the specified user or login, so that the user must have permissions on underlying objects. In this case, the caller must either:
    • Have execute permission on the code.
    • Be a sysadmin or db_owner, or have CONTROL SERVER >permissions on the server or the database, or have impersonate permission for the username.>
  • EXECUTE AS SELF: A shortcut notation for the current user who is creating the procedure. This is equivalent to EXECUTE AS USER = [myUserName]. The SQL Server catalog stores the actual user ID of the person who writes the code.
  • EXECUTE AS OWNER: Another variation of execution under the security context of a specific user, in this case the owner of the code at the time of execution. If the owner changes after the code is created in the database, it is the current owner when the code executes.

TIP: Any time the security context of the code changes through EXECUTE AS, the creator of the code must have the IMPERSONATE permission for the user specified in the clause. You don’t ever need to have this permission to impersonate yourself, however, such as for EXECUTE AS SELF.

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


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.

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.


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.


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.


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.

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!


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.

Understanding Logins with SQL Server

screen shot 2013-05-24 at 10.25.02 amSQL Server logins are not part of Windowsthey are saved in and managed by SQL Server. A user who connects to SQL Server via a SQL Server login is prompted for a login name and password. If you select the Windows Only mode of authentication, all SQL Server logins will be disabled and users will be able to connect only by using their Windows logins.

Mixed mode with SQL Server logins is much more flexible—for example, it supports users on Windows 9x computers—but it is also less secure. SQL Server logins and passwords are saved in the system tables in SQL Server, which are file-based. Users who gain access to these files could conceivably hack administrative logins and passwords.

If you configure your SQL Server to support SQL Server logins, there is one built-in SQL Server login that you need to watch out forthe sa login.

Beware of the sa Login

You may have noticed a login named “sa” hanging around with the other logins in the Logins node in Object Explorer. The sa, or system administrator, login is included mainly for backward compatibility with older versions of SQL Server. The sa account is mapped to the sysadmin fixed server role, and anyone who uses sa is a full system administrator, with irrevocable rights over the entire SQL Server instance and all the databases in it.

You can’t modify or delete the sa login. If you select Mixed Mode authentication when you install SQL Server, you’re prompted for a password for the sa user. Unless you set a password, anyone can log in as sa with no password, and play “let’s administer the server.” Needless to say, this is the last thing you want your users doing. Use the sa login only as a backdoor if other system administrators are unavailable or have forgotten their Windows passwords. If that happens, you probably need new admins!

WARNING! Never, EVER, use the sa login for access to a database in an application. Doing so could give a hacker administration-level control over your database server if the hacker is able to get control of the application. This has been an easy way to attack servers and is a horrible practice. Instead, either set up a custom Windows or SQL Server login for the application to use, and give that login the absolute minimum permissions necessary to run the application (which is another application of the principle of least privilege).

Password Policy and Enforcement

In versions of SQL Server before 2005, there was no easy way for a system administrator to enforce password policies that could help make a system more secure. For example, SQL Server had no way to force users to create strong passwords of a minimum length and a mix of alphanumeric and other characters. If someone wanted to create a login with a single letter for a password, you couldn’t configure SQL Server to prevent it. Likewise, there was no way to cause passwords to expire on a regular basis, such as every three months. Some people rightly saw this as a major reason not to use SQL Server logins.

SQL Server now can hook into the password policies of Windows Server 2003, Windows Vista, or later versions. The passwords are still stored in SQL Server, but SQL Server makes a call into the NetValidatePasswordPolicy() Windows API method, which was first introduced in Windows Server 2003. This API function applies the Windows password policy to SQL Server logins and returns a value that indicates whether the password is valid. SQL Server calls this function when a user creates, sets, or resets a password.

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.

Windows Logins via Transact-SQL

Transact-SQL provides full support for creating logins by using the CREATE LOGIN statement. The following example grants login privileges on SQL Server to the Windows user JaneAppDev on a machine named Willow (you’ll need to change the machine name if you want to try the code yourself). The brackets around the Windows login are required.

This statement adds the login but does not provide database access. You do that by using the CREATE USER statement. To add Jane to the AdventureWorks2012 database and make Production her default schema, add the following code:


You don’t have to name the database user the same as the server login. For example, you could instead name the user Jane in the database, assuming that it does not already contain a user Jane:

In this case, because the default schema wasn’t changed in the CREATE USER statement, the default would be dbo.

You can change a user account with the ALTER USER statement. For example, if you later wanted to make Jane’s default schema Production, you could use this code:

TIP: Only one user in any given database can be mapped to a single Windows login. A single login may, however, be mapped to users in many databases on the same SQL Server instance.

If you add one of the built-in Windows groups to SQL Server (Users, Power Users, etc.) use BUILTIN in the Windows login name instead of the machine or domain name. The following example grants login privileges to anyone in the Windows Users group. This means that anyone who can log in to Windows doesn’t have to log in a second time to get into SQL Server.

Thumbnail for 566This 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 Security: The Threats


Relational databases are used in an amazing variety of applications with connections from a dizzying array of clients, ranging from handheld devices to mainframe Web service applications. This activity exposes data over widely distributed networks, particularly the Internet, which makes it accessible to almost anyone, anywhere. The databases hold a significant portion of human knowledge, including highly sensitive personal information and critical data that makes international commerce work.

These characteristics make databases attractive targets for people who want to steal data or harm its owner by tampering with it. Making sure that your data is secure is a critical part of installing and configuring SQL Server and developing applications that use it to store data.

SQL Server has everything you need to secure your server and data against today’s sophisticated attacks. But before you can use these security features effectively, you need to understand the threats you face and a few basic security concepts.

The Threats

Identifying threats to a particular set of data and its server is an important first step in understanding how to configure and use SQL Server to protect the data. A database you create to manage your grade school soccer team’s equipment inventory probably doesn’t require heavy security measures. You’ll probably want to provide at least minimal access control so that a team member can’t just randomly change the record of who has which box of soccer balls.

On the other hand, if the database has personal data about the minors on the team, such as home addresses and phone numbers, you’ll probably want to step up security protections (and may be legally required to do so). You might protect the privacy of the data by segregating access so that almost anyone with access to the database can change the equipment data but only a select few can access the personal data. If the data includes mom and dad’s credit card number, you’ll need to go to extreme lengths to protect that data.

TIP: Sometimes the best way to protect data is simply not to put it in the database—for example, credit card numbers.

The following list is a sample of the kinds of threats your data may be susceptible to, but it is by no means an exhaustive list. Plenty of resources are available on the Web that can help you analyze the risks for your specific situation. This list is intended to help you start thinking about threats and how to use the features of SQL Server to counter them or at least reduce your data’s exposure to them.

  • Theft of data: Theft of data covers various types of unauthorized access to your data, whether by an outsider hacking into your network or an insider scanning for dirt on famous people. It may involve the thrill of reading forbidden information or be motivated by the sale of stolen credit card numbers.
  • Data vandalism: A hacker who gains access to your data can change it, which can cause a whole range of problems, from public embarrassment to shutting down your entire operation when all of your customer records are deleted.
  • Protecting data integrity: One of the biggest benefits of storing data in a relational database is that the database can help protect the integrity of the data. Data integrity includes mandating that every order have an associated customer, that a date stored in a date field really represents a calendar date, and that a percentage field contains only values between 0 and 100. Data integrity probably isn’t the first thing you think of in connection with security, but it is an important part of protecting your data.
  • Illegal storage: In the past, the data you collected during the course of business was your own business. But now myriad federal laws exist in the U.S., throughout the European Union, and other countries that control the kinds of personal data you can store, how you store it, and how you protect it. The penalties for violations can be severe—both monetary penalties and damage to the public image of your company. This is less a threat to data than a threat to your organization.

You have to understand the threats to your data to know how to protect against them. Don’t waste time on measures that don’t protect against specific threats to your data. You’ll never be able to cover all hypothetical situations, and at worst you’ll make your database server completely unusable by its intended users. Security is always a compromise that balances the risks against the time and money necessary to implement and maintain safeguards.

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.