Secrets of Foreign Key Index Binding

You might be surprised to learn that foreign keys bind to physical indexes when they are created. Furthermore, a foreign key does not necessarily bind to the primary key index of the referenced table; SQL Server allows a foreign key to refer to any column(s) that are guaranteed to be unique as enforced by a primary key constraint, unique constraint or unique index.

In this post, I’ll discuss the undocumented rules SQL Server uses to bind foreign key constraints to referenced table indexes so that you can achieve performance goals and protect yourself against unexpected errors in DDL modification scripts.

Background

Typically, one references the primary key in foreign key relationships. I’ve seen a foreign key (deliberately) reference columns other than the primary key only a couple of times in my career. The foreign key referenced an alternate key with a unique constraint in those cases. Why one would create such a relationship is an exercise for the reader. I’ll focus on the primary key here, although the same considerations apply to foreign keys referencing alternate keys.

As I mentioned earlier, SQL Server binds a foreign key to a physical unique index. This binding performance implications because it determines the index SQL Server uses to enforce referential integrity as child table rows are inserted or updated. Also, SQL Server will not allow the index bound to a foreign key to be dropped since that could allow duplicate rows in the parent table and thus break the unique side of the relationship. This must be considered when developing scripts that drop unique indexes (including primary key and unique constraints) that may be bound to foreign keys.

A foreign key referencing the primary key will always be bound to the primary key index when that is the only unique index on the foreign key column(s). However, you might have additional unique indexes on the primary key column(s) for performance reasons. For example, consider the case of a clustered primary key. Performance of a frequently executed query may be improved with a covering non-clustered index:

–create parent table

CREATE TABLE dbo.ParentTable(

       ParentTableID int NOT NULL IDENTITY

              CONSTRAINT PK_ParentTable PRIMARY KEY CLUSTERED

       ,Column1 int NOT NULL

       ,Column2 varchar(100) NOT NULL

       );

GO

 

–create a non-clustered covering index

CREATE UNIQUE NONCLUSTERED INDEX idx_ParentTable_ParentTableID

       ON dbo.ParentTable(ParentTableID) INCLUDE(Column1);

GO

 

INSERT INTO dbo.ParentTable VALUES(1, ‘some data’);

INSERT INTO dbo.ParentTable VALUES(2, ‘some data’);

INSERT INTO dbo.ParentTable VALUES(3, ‘some data’);

GO

 

–create child table

CREATE TABLE dbo.ChildTable(

       ChildTableID int NOT NULL IDENTITY

            CONSTRAINT PK_ChildTable PRIMARY KEY CLUSTERED

       ,ParentTableID int NOT NULL

              CONSTRAINT FK_ChildTable_ParentTable

                     FOREIGN KEY REFERENCES dbo.ParentTable(ParentTableID)

       );

GO

 

INSERT INTO dbo.ChildTable VALUES(1);

INSERT INTO dbo.ChildTable VALUES(1);

INSERT INTO dbo.ChildTable VALUES(1);

INSERT INTO dbo.ChildTable VALUES(1);

INSERT INTO dbo.ChildTable VALUES(2);

INSERT INTO dbo.ChildTable VALUES(2);

INSERT INTO dbo.ChildTable VALUES(2);

INSERT INTO dbo.ChildTable VALUES(2);

INSERT INTO dbo.ChildTable VALUES(3);

INSERT INTO dbo.ChildTable VALUES(3);

INSERT INTO dbo.ChildTable VALUES(3);

INSERT INTO dbo.ChildTable VALUES(3);

GO

 

UPDATE STATISTICS dbo.ParentTable;

UPDATE STATISTICS dbo.ChildTable;

GO

 

–show the foreign key index binding

SELECT

    fki.name

FROM sys.foreign_keys AS f

JOIN sys.indexes AS fki ON

      fki.object_id = f.referenced_object_id     

      AND fki.index_id = f.key_index_id

WHERE

      f.object_id = OBJECT_ID(N’dbo.FK_ChildTable_ParentTable’);

GO

 

–this query uses the covering index instead of clustered PK index

SELECT p.ParentTableID, p.Column1

FROM dbo.ParentTable AS p

WHERE p.ParentTableID IN(1,2,3);

GO

 

 

The SELECT query in the above script uses the covering idx_ParentTable_ParentTableID index. While this is good for performance, it introduces ambiguity regarding index binding to the foreign key. Again, any primary key constraint, unique constraint or index on the referenced column(s) may be referenced by a foreign key. With two candidate unique indexes (PK_ParentTable and idx_ParentTable_ParentTableID), you have little control which index is bound to the foreign key. 

SQL Server chooses the index binding based on rules that vary by version so you will get different binding depending on your version of SQLServer. SQL Server 2005 chooses the clustered index when possible and, if no suitable clustered index exists, the first (lowest index_id) unique non-clustered index on the referenced column(s) is used. The sample script above binds the foreign key to the PK_WideTable index under SQL Server 2005 because it is the clustered index, not because it is the primary key.

In later versions (SQL 2008, SQL 2008R2 and SQL 2012), the foreign key is bound to the unique non-clustered index on the referenced column(s) with the lowest index_id when possible. Only when no suitable unique non-clustered index exists is the unique clustered index chosen. So the foreign key in the above script is bound to idx_ParentTable_ParentTableID in SQL 2008 and later versions instead of the primary key index as one might expect.

Why Foreign Key Index Binding is Important

There are two reasons why it is important to control the index bound to a foreign key. One is performance. As I mentioned earlier, the index bound to the foreign key constraint is used at execution time to enforce the constraint as child table rows are inserted or the foreign key column(s) updated. If the parent table is large and not queried often but rows are inserted into the child table heavily, a unique non-clustered index that “covers” the referential integrity check may be more desirable than the clustered index. This can improve buffer efficiency and page life expectancy compared to using a clustered index (e.g. primary key). My assumption is that this is why SQL Server 2008 and later versions prefer the unique non-clustered index over the clustered index for constraint enforcement.

Another reason one should control the index bound to the foreign key is to facilitate index changes. If you try to drop an index bound to a foreign key, you’ll get an error like “An explicit DROP INDEX is not allowed on index ‘dbo.ParentTable.idx_ParentTable_ParentTableID ‘. It is being used for FOREIGN KEY constraint enforcement.” You’ll need to drop the foreign key first and recreate after dropping the index.

Since one can’t specify the bound foreign key index declaratively, the only guaranteed way to control the binding is to create the foreign key when only the desired unique index exists and create additional indexes afterward. This isn’t to say you can’t rely on the rules described earlier but you need to be aware that such rules vary depending on the SQL Server version and could change in the future. 

 

Internal SQL Server Database Version Numbers

A database created by a more recent version of SQL Server cannot be attached or restored to an earlier version. This restriction is simply because an older version cannot know about file format changes that were introduced in the newer release. 
If you attempt to attach a database to an earlier version, you will get SQL Server error 948 with the internal version numbers listed in the error message text. For example, the following error occurs if you try to attach a SQL Server 2008 R2 database to a SQL Server 2008 server:

The database ‘MyDatabase’ cannot be opened because it is version 665. This server supports version 661 and earlier. A downgrade path is not supported.

Sample text from SQL Server error 948
The cryptic version numbers in the error message refer to the internal database version. These internal version numbers are undocumented but are (at least currently) the same value reported by the DATABASEPROPERTYEX function ‘Version’ property of the source database. If you are unsure of the source database version, the table below maps the internal version numbers to SQL Server versions so you can determine the minimum version you need for the attach to succeed:

SQL Server Version

Internal Database Version

SQL Server 2008 R2

665

SQL Server 2008

661

SQL Server 2005 SP2+ with vardecimal enabled

612

SQL Server 2005

611

SQL Server 2000

539

SQL Server 7

515

SQL Server versions and internal database versions
Below are the allowable SQL Server upgrade paths for a database attach or restore. The internal database version will be as above after a successful attach or restore.

Target SQL Server Version

Source SQL Server Version

Internal Database Version

SQL Server 2008 R2

SQL Server 2008 R2

665

SQL Server 2008

661

SQL Server 2005 with vardecimal enabled

612

SQL Server 2005

611

SQL Server 2000

539

SQL Server 2008

SQL Server 2008

661

SQL Server 2005 with vardecimal enabled

612

SQL Server 2005

611

SQL Server 2000

539

SQL Server 2005 SP2+

SQL Server 2005 with vardecimal enabled

612

SQL Server 2005

611

SQL Server 2000

539

SQL Server 7

515

SQL Server 2005

SQL Server 2005

611

SQL Server 2000

539

SQL Server 7

515

SQL Server 2000

SQL Server 2000

539

SQL Server 7

515
SQL Server 7

SQL Server 7

515

Database File Versions and Upgrade Paths
As I mentioned earlier, downgrades are not supported. You’ll need to copy objects and data from the newer source database to the older target if you need to downgrade; attach or restore is not an option to copy a database to an earlier version.

Secret of SQL Trace Duration Column

Why would a trace of long-running queries not show all queries that exceeded the specified duration filter?  We have a server-side SQL Trace that includes RPC:Completed and SQL:BatchCompleted events with a filter on Duration >= 100000.  Nearly all of the queries on this busy OLTP server run in under this 100 millisecond threshold so any that appear in the trace are candidates for root cause analysis and/or performance tuning opportunities.

After an application experienced query timeouts, the DBA looked at the trace data to corroborate the problem.  Surprisingly, he found no long-running queries in the trace from the application that experienced the timeouts even though the application’s error log clearly showed detail of the problem (query text, duration, start time, etc.).  The trace did show, however, that there were hundreds of other long-running queries from different applications during the problem timeframe.  We later determined those queries were blocked by a large UPDATE query against a critical table that was inadvertently run during this busy period.

So why didn’t the trace include all of the long-running queries?  The reason is because the SQL Trace event duration doesn’t include the time a request was queued while awaiting a worker thread.  Remember that the server was under considerable stress at the time due to the severe blocking episode.  Most of the worker threads were in use by blocked queries and new requests were queued awaiting a worker to free up (a DMV query on the DAC connection will show this queuing: “SELECT scheduler_id, work_queue_count FROM sys.dm_os_schedulers;”).  Technically, those queued requests had not started.  As worker threads became available, queries were dequeued and completed quickly.  These weren’t included in the trace because the duration was under the 100ms duration filter.  The duration reflected the time it took to actually run the query but didn’t include the time queued waiting for a worker thread.

The important point here is that duration is not end-to-end response time.  Duration of RPC:Completed and SQL:BatchCompleted events doesn’t include time before a worker thread is assigned nor does it include the time required to return the last result buffer to the client.  In other words, duration only includes time after the worker thread is assigned until the last buffer is filled.  But be aware that duration does include the time need to return intermediate result set buffers back to the client, which is a factor when large query results are returned.  Clients that are slow in consuming results sets can increase the duration value reported by the trace “completed” events.

Why Parameters are a Best Practice

Parameterized queries are arguably the single most important database development best practice. Parameters, with proper SQL data types, provide many benefits. These include:

– improve security by preventing injection of unwanted SQL (barring non-parameterized server-side dynamic SQL)
– eliminate need to enclose literals in quotes (or not) depending on data type
– eliminate need prefix Unicode literal strings with N
– allow single quotes within strings without the need to escape them
– avoid the need to format date/time/datetime string literals in a particular way (which vary by culture)
– do not require decimal separators (which vary by culture)
– improve performance by reducing compilation costs and promoting execution plan cache reuse
– allow WHERE clause predicates with Always Encrypted columns
– code that’s cleaner and easier to maintain

Despite these many benefits, it is all too common to see applications build SQL statements by concatenating program variable values as SQL literals with values that vary by execution. I believe this simply due to ignorance because one often learns SQL using an ad-hoc tool like SSMS in order to focus on SQL syntax and database concepts, overlooking the important database programming aspects needed in practice. Developers unwittingly build SQL queries in application code as if they were typing SQL statements into an SSMS query window. Sadly, instructors sometimes teach database programming using string concatenation instead of parameterized queries and many online examples use improper techniques too, spreading this poor practice. When one posts questions with non-parameterized query code in online help forums, the egregious practice often distracts answerers from addressing the real question, attracts downvotes, and in some cases, a parameterized query would have avoided the need for the question to begin with.

This article discusses parameter basics in commonly used programming languages and Microsoft SQL Server APIs, along with code examples and best practices. Additionally, internals of how parameters are actually sent to SQL Server are described to provide insight into why parameterized queries are so important. This article focuses specifically on Microsoft SQL Server but parameters and their benefits apply to other DBMS products as well.

Introduction

Here’s a C# example of the common mistake of building an ad-hoc query with string concatenation:

A common misconception is that using stored procedures inherently prevent SQL injection. Stored procedures can help mitigate security risks but must also be called properly to release parameterization benefits. The same problems as the previous ad-hoc query exists with the stored procedure example below because parameter values are passed as literals.

Techniques for building parameterized SQL statements vary by client API, driver, programming language, and/or framework. The underlying concept is the same, though. Instead of including literals in SQL statements for values that differ for each execution, specify parameter tokens in their place and add parameters with values to the command separately. The SQL statement itself need not change between executions but parameter values can differ for each execution without modifying the SQL statement itself.

The parameter creation techniques used here are not the only ones available to create parameters. I’ve included documentation links that show other parameter creation methods and guidance as to which should be avoided.

Most ORM frameworks (Entity Framwork, Hibernate, et.al.) build parameterized queries on your behalf. These are beyond the focus of this article but I suggest you peruse the Parameters Under the Covers section of this article to ensure parameters are indeed being passed as expected as an RPC request by the framework you are using.

Parameters with ADO.NET

I’ll first mention one should use an ADO.NET and a managed ADO.NET provider in .NET applications whenever possible. ADO.NET does allow one to use unmanaged ODBC and OLE DB drivers via the System.Data.Odbc and System.Data.OleDb namespace objects but those are intended to be used only when no suitable managed ADO.NET provider is available. For Microsoft SQL Server, the managed providers are System.Data.SqlClient (included with the .NET framework) or the newer Microsoft.Data.SqlClient (.NET Core package, currently in preview). Managed providers provide the best performance in .NET applications by avoiding the cost of interop with unmanaged code, which is especially costly with large result sets.

Also, it’s not uncommon to see VB.NET applications sometimes use the legacy ADODB (ADO classic) API instead of ADO.NET because they started life in the VB6 world and were never changed to use ADO.NET. I’ll discuss ADO classic later in the context of unmanaged code. I suggest such lacy code be remediated to use ADO.NET if possible.

Below are C# parameterized equivalents of the earlier queries. These same objects and techniques can be used in VB.NET and managed C++ with only linguistic differences. The only change to the original SELECT statement is the parameter tokens within the query text plus adding parameters to the command’s Parameters collection (instead of concatenating values as SQL literal text). Parameter names (‘@’ prefix) are used as tokens for parameter values in SqlClient queries and allow for mnemonic names to make it easy to identify individual parameters within the query.

Importantly, note that parameter tokens within a query are never enclosed in quotes because these are not literals. Also, the same parameter may be specified more than once within a query with SqlClient without the need to add an additional parameter with the same value.

It’s generally best to specify CommandType.StoredProcedure when calling a stored procedure because this reduces server-side parsing and enforces that stored procedure parameter arguments be passed as client parameters instead of literals. With SqlClient and CommandType.StoredProcedure, only the stored procedure name is specified as the command text (i.e. no parameter tokens). The procedure name should be schema-qualified for maximum performance, a consideration in high-volume OLTP workloads. Parameters are added to the Parameters collection and implicitly mapped to the actual stored procedure parameters by name rather than by ordinal so the parameter collection order does not matter when multiple parameters are involved.

One can also execute a stored procedure as a parameterized query using a T-SQL EXECUTE statement and CommandType.Text similarly to a parameterized query. There is no benefit in doing so compared to CommandType.StoredProcedure for a single proc call (and it does slightly bloat the request). However, CommandType.Text allows one to execute a parameterized batch of multiple statements in a single round trip. The batch may be a mix of EXECUTE and other DML statements.

With an EXECUTE statement, stored procedure parameters can be specified using either named (i.e. @stored_proc_parameter_name1 = @command_parameter_name1, @stored_proc_parameter_name2 = @command_parameter_name2, …) or positional (@command_parameter_name1, @command_parameter_name2, …) arguments. Stating the obvious, positional syntax necessitates the parameter argument order on the EXECUTE statement match the stored procedure definition order. However, the order of parameters within the parameters collection isn’t significant with either named or positional arguments because the parameters collection is mapped to the parameters within the SQL text by name with SqlClient.

Below is a named parameter example. The stored procedure parameter name and the client parameter name are the same in this example.

Note that the EXECUTE keyword could have be omitted in this example because SQL Server assumes the first statement of a batch is EXECUTE. I recommend the EXECUTE be explicitly specified even when the first statement in a batch.

Parameters with ADO.NET System.Data.OleDb

Again, .NET applications should always use a managed provider when one is available (SqlClient in the case of SQL Server). That being said, I use unmanaged SQL Server drivers in these examples to illustrate how queries can be parameterized in ADO.NET for other DBMS products that don’t have a managed .NET provider. The only difference would be the connection string.

Here are the equivalent OLE DB parameterized queries in ADO.NET using the latest SQL Server OLE DB provider. The differences compared to same SqlClient query are the connection string, question mark parameter tokens, and the System.Data.OleDb objects.

Parameters with ADO.NET System.Data.Odbc

Below is the ODBC parameterized query in ADO.NET. The only difference compared to the earlier OLE DB version is the connection string and System.Data.Odbc namespace objects. Although this example uses a DSN-less connection string, one could also specify an ODBC DSN in the connection string (e.g. “DSN=YourDSN”) and omit the attributes that are redundant with the DSN configuration.

Similarly, here is the ODBC version of the same previous stored procedure calls. However, with ODBC and CommandType.StoredProcedure, the command text specifies an ODBC procedure call escape sequence with parameter marker(s) rather than just the stored procedure name.

Parameters with ADO Classic

ADO “Classic” (not to be confused with ADO.NET) is a mature legacy technology that hasn’t been enhanced for about 20 years. An unmanaged COM-based API, ADO classic is still used today in classic ASP web applications, VBA (Visual Basic for Applications), legacy VB 6 code, and VB.NET code that was converted from VB 6 without being refactored to use modern managed ADO.NET APIs.

It is best to use an OLE DB driver with ADO classic because these unmanaged APIs natively use COM-based technologies. That being said, one can use ODBC (which is ultimately a call-level interface rather than COM) with ADO via the Microsoft OLE DB Provider for ODBC drivers (MSDASQL, which is the default OLE DB provider when no provider is specified). This is needed when no OLE DB provider available for a non SQL Server DBMS but should otherwise be avoided since ADO must go through the extra MSDASQL layer. A SQL Server OLE DB provider/driver (i.e. SQLOLEDB, SQLNCLI, and MSOLEDBSQL) should be used directly to access SQL Server from ADO classic.

Regardless of the underlying driver, ADO programming and parameterization is similar with either OLE DB or ODBC. The ADO classic object model is different than its ADO.NET cousin but parameterization concepts and techniques are similar. I’ve included C# examples here only to illustrate the ADO parameter object model differences compared to the previous ADO.NET examples. In practice, one would not typically use ADO classic in managed code.

Parameterized ADO query example:

Parameterized ADO proc call example:

ADO Parameters with VBScript

Below is an example stand-alone VBScript example of the query and proc call.

ADO Parameters with ASP classic

The ADO code for classic ASP is the same as the preceding stand-alone VBScript example except that the objects are instantiated using the IIS Server.CreateObject method:

Parameters Under the Covers

The job of all SQL Server client APIs regardless of programming language is to manage the Tabular Data Stream (TDS) protocol session used for SQL Server communication. This involves establishing the session (network connection and authentication), converting application requests into internal TDS request messages (e.g. batch and RPC requests), sending those to SQL Server, and receiving the results returned by SQL Server over TDS. Although details of the TDS protocol session are an implementation detail transparent to applications, delving into the way TDS requests are sent to the database engine provides valuable insight into the concepts, benefits, and use of parameters in the SQL Server world.

When a non-parameterized query is executed (i.e. an empty parameter collection and CommandType.Text in ADO.NET), the client API sends the command text to SQL Server as a SQL batch request over TDS. There is no provision for parameters in a SQL batch request. The database engine parses the batch text (which may contain multiple statements), generates an execution plan, and executes the batch. If the batch text (including literal values) exactly matches that of a previously executed query (or an auto-parameterized query) that is still cached, the plan is reused. Because a batch request has no parameters, the application must include T-SQL literals within the text for values that vary by execution. This requires the app code to build the query by concatenating SQL language text with literal strings and for SQL Server to parse those literals.

The onus is on the application to construct literal strings embedded within the SQL batch text properly so that they are parsed as desired by SQL Server. This necessitates enclosing literal values in single quotes when required and formatting the string value according to the data type it represents. The app code must double up single quotes within string literal values so that it’s interpreted as a quote instead of literal enclosure. Date/time literals should be formatted as ISO 8601 literal values to ensure the intended value is used regardless of session the DATEFORMAT setting, a common mistake with non-parameterized queries. Decimal literals must specify the ANSI/ISO standard period as the decimal separator instead of a comma used by some cultures. Source values must be validated to ensure adherence to the intended data type and not interpreted as SQL language elements (injecting unintended SQL).

Parameterized queries (e.g. non-empty parameters collection or CommandType.StoredProcedure) are processed differently. The client API sends the command to SQL Server as a remote procedure call (RPC batch) TDS request instead of a SQL batch request. This RPC batch request includes the batch text with the supplied parameter tokens (e.g. CommandType.Text) or stored procedure (e.g. CommandType.StoredProcedure). The actual parameters, containing parameter definition meta-data and values as specified by the client application, are included in separate structures as part the TDS RPC request stream sent to SQL Server. Most importantly, the actual parameter values as passed as native data types as specified by the client application. This has a many positive implications.

The database engine need not parse parameter values since they are already in the native structure of the specified parameter data type, such as a 32-bit integer, datetime structure, decimal structure, Unicode string, etc. SQL Server only checks that parameter values adhere to the TDS protocol spec and then uses the supplied parameter values directly without parsing. There is no notion of datetime formatting, string escape sequences, decimal separators, etc. because no parsed occurs. Unwanted SQL cannot be injected via parameters (barring server-side dynamic SQL) because the values are not part of a T-SQL statement.

I’ll add that the query text you see in a server-side trace of an RPC request is not necessarily what SQL Server actually received by the client API. The query text in trace data is actually a reversed-engineered rendering of the TDS protocol request in familiar T-SQL format. For example, when you pass a table-valued parameter, you’ll see an RPC batch request with batch text containing table variable declared, a bunch of INSERT statements into the variable, followed by it’s use as a parameter in the query or stored procedure. In actuality, the TVP column meta-data tokens were passed over TDS followed by the stream of TVP rows. The database engine bulk-inserted the TVP data into a tempdb structure matching the column meta-data, not individual inserts as the trace suggested.

Similarly, scalar parameter values will show as literal representations in the trace instead of the actual binary structure passed. For example, a datetime value will show as a ‘yyyy-MM-dd HH:mm:ss.fff’ literal (not an unambiguous ISO 8601 datetime format) rather than the actual binary datetime structure passed to SQL Server.

Much of this is implementation details but something you should be aware of when interpreting RPC requests in traces.

Best Practices

Parameter definitions should specify the desired data type, maximum length, precision, and scale of each parameter. Attention to these parameter definition details will help maximize performance by avoiding implicit conversions, promoting sargable expressions, and reusable cached plans.

A common problem (i.e. mistake) is using the AddWithValue method to create parameters. AddWithValue infers the data type and length based on the client variable value and may result in non-sargable expressions and cache bloat as I detailed in this post. The bottom line is that one should use a method that allows specification of the desired SqlDbType, length, etc. such as Add.

Security with Ownership Chains

Security with Ownership Chains

 

Ownership chaining is one of my favorite SQL Server security features.  I like ownership chaining because it allows me to confine data access to specific stored procedures, views and functions while preventing direct access to the underlying tables.  This approach provides a well-defined application interface that insulates applications from the physical implementation and allows me to change things on the back-end as long as I don’t change the interface.  Also, thanks to ownership chaining, I have many options to vertically and horizontally partition data to meet security objectives while ensuring tables are not accessed directly.  

Ignorance is not bliss when it comes to ownership chaining security, though.  I’ve seen cases where data access was inadvertently allowed because ownership chaining wasn’t considered.  It is imperative that SQL Server DBAs understand ownership chaining in the SQL Server security model so that data is properly secured.  I want to point out a couple of important security concepts as it relates to ownership chaining with the hope that it will help SQL Server DBAs implement a security scheme that is appropriate for their environment and also easy to manage.

Ownership Chain Behavior

SQL Server always checks permissions on directly referenced objects using the current connection security context.  However, permissions are evaluated differently when objects are accessed indirectly, such as those used in a trigger, view or stored procedure.  The behavior of ownership chaining is that object permissions are not evaluated when the calling and referenced object owners are the same.  Also, because object permission checking is short-circuited in an unbroken chain, ownership chaining takes precedence over an impersonated security context (EXECUTE AS clause) as well as denied object permissions.   Consequently, permissions on indirectly referenced objects are irrelevant in an unbroken ownership chain.  This ownership chaining behavior gives a DBA control over exactly who can use which objects while preventing ad-hoc access to other objects.

Ownership chaining behavior might not be intuitive at first.  I like to think of it from the perspective that the purpose of granting object permissions is so the object can actually be used.  For example, it is of no use to grant a user SELECT permissions on a view unless data can be retrieved from the view.  The same applies even when a stored procedure or function impersonates with EXECUTE AS; the impersonated principal must be able to use objects referenced by the proc in order for the procedure to be useful.  I keep in mind that with ownership chaining, I effectively grant permissions on exposed object functionality regardless of how the functionality is implemented.

Ownership chaining is limited to a single owner in order to prevent escalation of privileges in environments where non-privileged users can create objects.  Ownership chaining also does not apply to dynamic SQL executed from a stored procedure; the invoking (or impersonated) user needs permissions on objects referenced by dynamic SQL statements.  I should add that a one of the useful purposes of EXECUTE AS and signing modules with certificates is to provide needed permissions in a broken ownership chain or granting statement permissions without granting user permissions directly.  See SQL Server MVP Erland Sommarskog’s article Giving Permissions through Stored Procedures for a discussion of these techniques.

Ownership Best Practices

Ownership is obviously central to ownership chaining.  In the vast majority of environments, it’s probably best to simply inherit the owner (a.k.a. AUTHORIZATION) from the object’s schema instead of overriding on individual objects.  It is possible to assign ownership to individual objects (using ALTER AUTHORIZATION) in order to implement chaining that is independent of schema but I think this is overly complex for most environments.  One reason I can think of why one might specify a different owner at the object level is in the case where a table contains particularly sensitive data and one wants to deliberately prevent inadvertent access via ownership chaining.  I think such cases are rare, though. 

I described some of the practices I follow for schema and object ownership in Keep Schema and Ownership Simple.  In summary, I specify dbo as the schema authorization (owner) unless I want to break the ownership chain between schemas to create a security boundary.  When a security boundary is desired, I create a role (or user without login) with the same name as the schema for the schema authorization.

Keep Schema and Ownership Simple

I like to keep things simple because simplicity is easier to manage and less prone to error.  When I’m faced with schema design decisions, I pick the selection with the least complexity that meets my objectives.  Here are some of my thoughts regarding schema and ownership in SQL 2005.

Schema

A schema is basically a container that categorizes database objects and simplifies security administration.  As a namespace, schemas logically organize objects without the need for special object naming rules.  Different objects can have the same name as long as they exist in different schemas because the schema name is essentially an extension of the object name that will “uniqueify” the name within a database. 

Categorizing objects by schema is particularly useful in complex databases with many objects.  There is some subjectivity on exactly how one might draw schema boundaries but the basic concept is the same; group related objects into different schema to provide organization to an otherwise unwieldy schema.  Classifying related objects by schema makes complex databases easier to understand and manage.

Schema also simplifies security administration because permissions can be granted en mass at the schema level.  For example, I can grant EXECUTE permissions on all objects in a schema with a single statement like “GRANT EXECUTE ON SCHEMA::Sales TO SalesRole”.  I can grant CONTROL permissions on a schema to allow privileged users to full control over a specific schema but not others in the same database.

Even with the option to use multiple schemas, I tend to use the built-in dbo schema.  I do this because most of the applications I maintain were developed before SQL 2005 and all objects are already in the dbo schema.  Some of those legacy systems could benefit from multiple schemas but I’ll continue to use dbo for those applications to be consistent until I need to add a group of new objects that are appropriate for a separate schema.  The new SQL 2005 databases I’ve developed thus far have been fairly simple and haven’t warranted using multiple schemas for either classification or security purposes.

Ownership

The owner is important for two main reasons:  1) the owner has powerful CONTROL permissions over all owned objects and 2) the owner determines whether or not the ownership chain is broken between objects.  Schema-contained objects will inherit the schema owner unless explicitly overridden using ALTER AUTHORIZATION.  Personally, I think it best for objects to inherit the schema owner in the vast majority of cases; if an object warrants a different owner than the schema, the object probably belongs in a different schema.

I use the built-in dbo principal for ownership unless I have a reason to do otherwise.  This approach is perfect in environments where only db-owner role members can create objects and schemas are used solely as a namespace rather than a security boundary.  The dbo principal exists in all databases so there is no a need to create a user or role for ownership purposes.  Simple is good.

Different schema owners provide a security boundary between objects in different schema because this breaks the ownership chain.  With a broken chain, explicit permissions on indirectly referenced objects are needed by the end user or the impersonated principal.  Different schema owners ensure that I don’t inadvertently provide access to data in different schema via ownership chaining.

Note that an owner can be any database principal and does not necessarily need to be associated with a login.  I find this feature especially handy in situations where I want to specify an owner other than dbo.  Since I can specify an owner that is not a real person, I won’t need to change ownership if the owner leaves the company or moves on to other roles in the organization. 

It’s probably best to create a principal (role or a user without login) with the same name as the schema for ownership purposes when different owners are desired.  The only case I can think of where it might be appropriate for a real person to own a schema (at least initially) is in a development environment when non-dbo users might create schemas and objects.

Database Owner Troubles

Do you know who owns your databases?  Execute sp_helpdb on your SQL Server instances and you might find some surprises under the “owner” column.  It isn’t uncommon to see accounts of people who have left the company or moved on to other roles in the organization that don’t require privileged database access.  Yet these owners still have full database permissions, including the ability to drop the database.  To prevent these security issues and other problems, consider establishing an appropriate database ownership standard for your environments.

Database ownership is an often forgotten detail because it is implicitly set to the database creator’s account.  The owner will initially be a Windows account or SQL login, depending on the authentication method used by the creator.  Note that the owner is always an individual account, not a group or role, so a database created by a sysadmin role member is actually owned by the creator’s individual account instead of a built-in security principal (unless the creator logged in using the “sa” account).

A Best Practice is to change the database owner immediately after creating, restoring or attaching a database.  Unless I have a reason to do otherwise, I specify “sa” as the database owner.  This can be done with sp_changedbowner in SQL 2000 or with ALTER AUTHORIZATION in SQL 2005:

SQL 2000:

EXEC MyDatabase..sp_changedbowner ‘sa’;

SQL 2005 and SQL 2008

ALTER AUTHORIZATION ON DATABASE::MyDatabase to sa;

 

The Significance of the Database Owner

Database ownership is important from a security perspective because the owner account is mapped to the built-in “dbo” user.   The “dbo” user, sysadmin role members and db_owner role members all have full database permissions and can also DROP the database.  The database owner is also used as the authorization of the “dbo” schema, which comes into play with ownership chaining.  With cross-database chaining, the databases involved must have the same owner in order to provide an unbroken chain for “dbo” schema objects.

A difference between the database owner and db_owner role members is that there is exactly one “dbo” user (the database owner) but there may be many users that are db_owner role members.  The owner’s account cannot be explicitly added to the database because the owner is already implicitly mapped to the “dbo” user and an account can be mapped to no more than one user per database.  If you attempt to add the owner as a database user, error message “The proposed new database owner is already a user or aliased in the database” results.

 

Troubleshooting Database Ownership

The database owner is ultimately identified by the account SID (security identifier).  The creator’s account SID is recorded in 2 places:  1)  at the server level in sys.databases/sysdatabases and  2)  in the database as the dbo user SID in sys.database_principals/sysusers.  These SIDs will normally match but can get out-of-sync following a database restore or attach.  You will also end up with a NULL database owner if the owner’s Windows account is deleted because of the orphaned SID.

Mismatched owner SIDs can result in problems such as

·         Problems executing system stored procedures

·         Problems with tools

·         Broken cross-database ownership chains

The sample queries below will help identify problem database owners.  Problem owners will have mismatched SIDs and/or NULL owner names.  Fortunately, problem database ownership is easy to remedy.  Simply change the database owner using sp_changedbowner or ALTER AUTHORIZATION as you would after creating a new database.  In some cases, you might get an erroneous “The proposed new database owner is already a user or aliased in the database” due to the mismatch.  A workaround in this situation is to temporarily change the database owner to a non-conflicting login and then back to the desired owner.

 

SQL 2000:

IF OBJECT_ID(N‘tempdb..#owners’, ‘U’) IS NOT NULL

      DROP TABLE #owners;

 

CREATE TABLE #owners

(

      database_name sysname NOT NULL,

      sys_databases_sid varbinary(85) NOT NULL,

      sys_databases_owner nvarchar(256) NULL,

      sys_users_sid varbinary(85) NULL,

      sys_users_owner nvarchar(256) NULL

);

 

INSERT INTO #owners

      (

            database_name,

            sys_databases_sid,

            sys_databases_owner

      )

      SELECT

            name,

            sid,

            SUSER_SNAME(sid)

      FROM master.dbo.sysdatabases;

 

EXEC sp_MSforeachdb

      UPDATE #owners

      SET sys_users_sid = (

                  SELECT sid

                  FROM [?].dbo.sysusers

                  WHERE name = ”dbo”),

            sys_users_owner = (

                  SELECT SUSER_SNAME(sid)

                  FROM [?].dbo.sysusers

                  WHERE name = ”dbo”)

      WHERE database_name = ”?”

      ;

 

SELECT * FROM #owners

WHERE sys_databases_sid <> sys_users_sid;

 

IF OBJECT_ID(N‘tempdb..#owners’, ‘U’) IS NOT NULL

      DROP TABLE #owners;

GO

SQL 2005:

IF OBJECT_ID(N‘tempdb..#owners’, ‘U’) IS NOT NULL

      DROP TABLE #owners;

 

CREATE TABLE #owners

(

      database_name sysname NOT NULL,

      sys_databases_sid varbinary(85) NOT NULL,

      sys_databases_owner nvarchar(256) NULL,

      sys_users_sid varbinary(85) NULL,

      sys_users_owner nvarchar(256) NULL

);

 

INSERT INTO #owners

      (

            database_name,

            sys_databases_sid,

            sys_databases_owner

      )

      SELECT

            name,

            owner_sid,

            SUSER_SNAME(owner_sid)

      FROM sys.databases;

 

EXEC sp_MSforeachdb

      UPDATE #owners

      SET sys_users_sid = (

                  SELECT sid

                  FROM [?].sys.database_principals

                  WHERE name = ”dbo”),

            sys_users_owner = (

                  SELECT SUSER_SNAME(sid)

                  FROM [?].sys.database_principals

                  WHERE name = ”dbo”)

      WHERE database_name = ”?”

      ;

 

SELECT * FROM #owners

WHERE

      sys_databases_sid <> sys_users_sid

      OR sys_databases_owner IS NULL;

 

IF OBJECT_ID(N‘tempdb..#owners’, ‘U’) IS NOT NULL

      DROP TABLE #owners;

GO

 

Avoid Causing Problems with Profiler

The last thing you want to do is introduce instability when gathering useful performance and troubleshooting information via Profiler.  However, I’ve found that many DBAs are not aware that Profiler and SQL Trace need to be used carefully in order to minimize overhead that can negatively affect overall SQL Server performance and lead to problems like query timeouts.  I want to get the word out on how to use these invaluable tools with minimal server impact.

First, here’s a little background on SQL Trace architecture.  SQL Trace runs in the SQL Server process to gather and filter traced events.  A trace can either write directly to a file (server-side trace) or return data directly to an application like Profiler in near real-time.  In both cases, traces are ultimately created and managed using documented stored procedures (sp_trace_create, sp_trace_setevent, sp_trace_setfilter and sp_trace_setstatus).  However, instead of calling these stored procedures directly, SQL 2005 Profiler uses the SMO API which wraps these stored procedure calls and well as calls to an undocumented procedure to retrieve trace data.

When an event occurs inside of SQL Server, SQL Trace passes the event to all running traces that include the event.  Each trace then filters the event as specified by the trace definition and queues the selected event data before either being written directly to a file or returned to the Profiler instance (or other SMO-based application).

Writing trace data directly to a file is very efficient since it’s done directly by SQL Server with minimal overhead.  The active trace file can be viewed using the fn_trace_gettable() table-valued function on the same instance as the trace.  However, the trace needs to be stopped or a file rollover must occur before the trace data can be imported into Profiler or accessed from another SQL Server instance using fn_trace_gettable().

Profiler, on the other hand, retrieves and processes trace data in near real time.  The drawback is that the interactive graphical display requires quite a bit more overhead than writing directly to a file and more resources are also needed to transfer the trace data from SQL Server to the application.  This overhead isn’t typically noticeable unless the trace generates a lot of events or the server is low on resources.  However, I’ve seen cases where a high-volume Profiler trace degrades overall server performance to the point of causing query timeouts and slow response time.  SQL Server MVP Linchi Shea’s post Performance Impact: Profiler Tracing vs. Server Side SQL Tracing provides metrics that validate my experiences.

I routinely use server-side SQL Trace scripts instead of Profiler on production servers to minimize trace resource usage.  Since it’s quite tedious to create a trace script from scratch, I use Profiler against a development server to create the desired trace and then script the trace from the Profiler menu (FileàExportàScript Trace Definition).  I then change the script to specify the trace file path, max file size and rollover option.  I finally run the script on the production server to start the trace and make note of the TraceID that will be needed to stop and delete the trace with sp_trace_setstatus.  Function fn_trace_getinfo(DEFAULT) comes in handy if I forget the TraceID or want to list currently defined traces.

The Optimizing SQL Trace in the Books Online lists some SQL Trace performance guidelines.  Here are some of those plus some of my own:

  • Run Profiler remotely instead of directly on server
  • Avoid including events that occur frequently (e.g. Lock:Acquired) unless absolutely needed
  • Include only event classes needed
  • Specify limiting filters to reduce the number of events
  • Avoid redundant data (e.g. SQL:BatchStarting and SQL:BatchCompleted)
  • Avoid running large traces with Profiler; consider a server-side SQL Trace instead
  • Limit server-side trace file size and manage space usage

I hope these tips help you avoid introducing performance problems when using Profiler and SQL Trace.

Use Caution with Explicit Transactions in Stored Procedures

Use Caution with Explicit Transactions in Stored Procedures

 

 

 

Explicit transactions are often used within stored procedures to guarantee all-or-nothing data integrity.  However, a little known fact is that a query timeout will leave the transaction open unless non-default session settings and/or special exception handling are used.  I’ll describe how to protect your application from problems following timeouts and other unexpected errors.

Consider the following stored procedure containing an explicit transaction:

You execute the script below from SQL Server Management Studio or Query Analyzer with the query timeout option set to 30 seconds and the second UPDATE statement in the proc times out. 

timeout option set to 30 seconds and the second UPDATE statement in the proc times out.

Assuming default session settings, check all that apply:

a)      Proc execution continues after the failed UPDATE

b)      @@ERROR is zero

c)       @@TRANCOUNT is zero

d)      The PRINT statement is executed

Let me first mention something important about timeouts before I provide the correct answer(s).  A command timeout occurs in the client application, not the SQL Server backend.  A timeout is basically just a cancel request that is sent by the client API when a command executes longer than the specified interval.  A timeout is very much like pressing the stop button in Query Analyzer or Management Studio because you feel a query has been running too long.  The only difference is that the stop is issued by the client API on behalf of the application.

Both “A” (proc continues) and “D” (PRINT executes) are false because the attention event from the client instructed SQL Server to cancel the currently executing batch in its entirety.  No code after the UPDATE executes, including the PRINT statement following the stored procedure execute.  This is logical since a query cancel or timeout wouldn’t be much use if SQL Server continued executing statements afterward.

“B” (zero @@ERROR) is true.  @@ERROR is zero because no error occurred on the backed; SQL Server successfully canceled the batch per the client cancel request after the timeout.  The timeout error is raised only on the client by the API to notify the application (SSMS/QA in this example) that the command timed out.  SSMS and QA simply catch the error and display the error message from the API.

“C” (zero @@TRANCOINT) is false because XACT_ABORT OFF is the default session setting.  With XACT_ABORT OFF, it is the client application’s responsibility to trap the timeout error and rollback the transaction if necessary.  The transaction is left open and uncommitted following the timeout error.  This can have serious and undesirable consequences if the application performs other work on the connection, unaware of the open transaction.

Using SET XACT_ABORT

SET XACT_ABORT specifies what action SQL Server should take following run-time errors.  The default session setting is SET XACT_ABORT OFF, which indicates that only the Transact-SQL statement that raised the error is rolled back and the transaction continues.  Depending on the severity of the error, the entire transaction may be rolled back and batch aborted, even with SET XACT_ABORT is OFF.   

A side effect of SET XACT_ABORT OFF is that a cancel/timeout error can leave an open transaction so it’s the client’s responsibility to cleanup following cancel/timeout.  To safeguard against leaving an open transaction, applications that execute transactions with SET XACT_ABORT OFF need to roll back transactions and perhaps close the connection following SQL exceptions.  

Note that with connection pooling, simply closing the connection without a rollback will only return the connection to the pool and the transaction will remain open until later reused or removed from the pool.  This can result in locks begin held unnecessary and cause other timeouts and rolling blocks.

SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs.  Compile errors (e.g. syntax errors) are not affected by SET XACT_ABORT. 

In my experience, SET XACT_ABORT ON provides the desired behavior in most cases.  I’ve never run into a situation where I wouldn’t want to rollback a transaction following a cancel or timeout.   I nearly always specify SET XACT_ABORT ON in stored procedures that contain explicit transactions to ensure that transactions are rolled back even if the application code doesn’t clean up properly.  The only time I don’t use XACT_ABORT is in rare cases where I need to trap and handle specific errors in Transact-SQL and continue.

I strongly recommend that SET XACT_ABORT ON be included in all stored procedures with explicit transactions unless you have a specific reason to do otherwise.  The consequences of an application unwittingly performing work on a connection with an open transaction are disastrous.

SQL Server error handling in general is a huge topic I focused on only on timeout errors and SET XACT_ABORT here.  For a thorough discussion of SQL Server error handling, I suggest perusing articles Implementing Error Handling with Stored Procedures and Error Handling in SQL Server – a Background by SQL Server MVP Erland Sommarskog.

 

SQL Trace Parameter values are not always as they seem

I stumbled across surprising SQL Trace/Profiler behavior I think is worth mentioning.  Parameter values reported in trace RPC starting/completed events are not the values that SQL Server uses to execute the query.  Here’s an example I recently discovered that shows this behavior.

 

I ran the following C# code to execute parameterized query “SELECT @DateTime” with the parameter value set to October 11, 2007.  The console message verified that SQL Server returned the expected date.

 C# Code 

Here is the SQL Profiler trace of the of the SQL:BatchCompleted and RPC:Completed events:

Appliction SQL Trace

I pasted the script from the trace and ran it from a SQL Server Management Studio query window.  Here’s the trace of the SSMS script execution:

SSMS SQL Trace

Even though the SQL looks the same, the SSMS query returned a different date (“November 10, 2007”) than the application (“October 11, 2007”)!  What’s up with that?  Why would the same script return different values when run from application code vs. Management Studio (or Query Analyzer), even with identical session settings?

The reason for the behavior difference is the trace event class.  The application trace showed an RPC:Completed event but the SSMS session trace showed SQL:BatchCompleted.  Both had the same “exec sp_executesql…” statement in the TextData.  Even though the trace reported the same TextData for both events, SQL Server processed the RPC differently than the SQL batch and the trace didn’t show the complete story for the RPC event.

The issue is that the datetime input parameter value for the RPC event was not really included in the SQL statement like the trace RPC:Completed event showed.  The actual datetime parameter value that SQL Server used during RPC execution was passed in native (i.e. binary) format via the low-level TDS protocol.  The trace TextData was only a reverse-engineered string representation of that value rather than the actual value SQL Server used.

In contrast, the batch sent by SSMS wasn’t parameterized so SQL Server needed to parse the datetime string value in the batch text.  The datetime string “2007-10-11 00:00:00:000” (generated by the trace, not passed by the app) is ambiguous and interpreted differently depending on the DATEFORMAT setting.  Due to the “DATEFORMAT DMY” setting in the script, the date string value was (mis)interpreted as November 10, 2007 instead of October 11, 2007.  The DATEFORMAT setting had no affect on the RPC (parameterized value) because the setting affects only string parsing and not the native datetime value provided by the application.

I discovered this issue when I was helping a user who was working with a French language SQL Server (that’s why I specified DATEFORMAT DMY) troubleshoot a datetime parameter problem.  The trace data led me down the wrong path because it didn’t immediately click with me that 1) DATEFORMAT doesn’t affect RPC datetime parameters and 2) trace RPC TextData isn’t used for query execution anyway. 

I filed Connect feedback on this to suggest that SQL Trace/Profiler be changed to serialize RPC datetime values in a DATEFORMAT neutral format like “2007-10-11T00:00:00:000” or “20071011 00:00:00:000”.  This will provide the same behavior when the trace TextData SQL is executed as a batch and be a bit more intuitive when analyzing trace data.