Tag Archives: SQL Server 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.

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.

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.

SQL Server Processors & Security Options

SQL Server 2012 supports multiple processors if they are available on the server. The Processors page, shown in the figure below on a dual-processor machine with the processor information expanded, lets you control how SQL Server uses multiple processors.

sql1ch02_blogprocessors page of server properties

The list of processors lets you specify which processors to use for this instance of SQL Server. You can change the Processor Affinity to dedicate one or more processors to SQL Server and reduce some of the overhead of moving threads between processors. This change can help the database server’s performance but may hinder Windows’ performance. I/O Affinity binds disk input/output operations to one or more processors. Using this option essentially binds I/O threads to the specified processors.

On multiprocessor machines, the check boxes below the list of processors save you time by letting you change the options for all processors. When you check either or both options, the corresponding options in the grid above are disabled.

The lower part of the page lets you control threading. You can set the maximum worker threads, which helps limit the resources consumed on servers with hundreds of client connections, since normally a new Windows thread is created for each connection. Setting a maximum can hurt performance because once the limit is reached, any new connection attempts must wait for another connection to close before accessing the server.

You can also boost the priority of SQL Server threads. This can result in more processor time relative to other processes on the server, but can actually hurt performance if essential Windows functions are unable to run efficiently.

Keep in mind that changing processor options reduces SQL Server’s ability to manage its own resources. You should consider doing so only when you have a situation that SQL Server does not seem to be handling appropriately.

Security Options

Security is a critical feature in SQL Server 2012, and the Security options page in Server Properties lets you configure a few settings, shown below. Probably the most important setting on this page is the server authentication mode, either Windows authentication alone or mixed mode in which both SQL Server and Windows authentication are available.

security options in server properties

The login auditing options control which authentication operations are logged in the Windows event logs. The default is Failed logins only, which is a great tool to alert you to some types of attacks, such as a dictionary attack on user names and passwords. The more information you log the better chance you have of troubleshooting, but the greater the effect on performance.

You can set up a proxy account for use when calling the xp_cmdshell extended stored procedure, which runs code in a Windows command shell. Code that runs in the command shell has the permissions of the specified user. Make sure that whatever account you specify here has the least privileges necessary for the tasks you’ll perform in the command shell. Otherwise, an attacker who gains control of SQL Server can run high-level operations from the command shell.

The C2 audit tracing option configures the server to record attempts to access Transact-SQL statements and database objects. This saves a huge amount of information, so you’ll need to monitor the disk files to make sure that you don’t run out of disk space, which can cause SQL Server to shut itself down to prevent attacks. The cross-database chaining option can enable ownership chaining across SQL Server instances. This is a potential security hole, so you should have a good reason to enable this option.

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