Inconsistency in behavior with contained databases

Question

The error message that I'm getting doesn't make any sense and isn't consistent.  When you have a database set to partial containment, temp table features don't behave consistently.  If my current context is the contained database, I can create a temp table with a primary key using only one of the syntax options.  If you decorate the column with the PRIMARY KEY keyword and let the system generate a name for the pk, it works.  If you try to use the CONSTRAINT clause and explicitly name the primary key, it fails.  Obviously, you also can't alter the table and add a primary key constraint. The primary key gets a name, regardless of how you create the temp table.  If you change context to tempdb, you don't get an error when tgrying to create the temp table using either syntax format.  What does giving the primary key your own name instead of the system generating it have to do with containment?

Here is a repro:

create database test
go
alter database test set containment = partial
go
use test
go
create table #test1
(Col1   int identity(1,1) primary key)

create table #test2
(Col1   int identity(1,1),
constraint pk_test primary key (Col1))
go
drop database test
go

Msg 12827, Level 16, State 1, Line 1
User-named PRIMARY KEY constraint 'pk_test' is not allowed on temp table '#test2' because it is being created in a contained database. Please consult the Books Online topic Understanding Contained Databases for more information on contained databases.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

 

---

Mike Hotek [e-mail]

Answer 1

I don't really see where "security" comes into play here, Mike?

Say that you have a procedure that goes:

CREATE PROCEDURE bad_sp AS
CREATE TABLE #t (a int NOT NULL,
                 CONSTRAINT pk PRIMARY KEY(a))
go

If you move the contained database to another server, and this server already has an object named "pk" in tempdb, the procedure will not run on that server. So there is a breach against containment.

Of course, if two users run the procedure simultaneously, things also blow up, so you could argue that containment is only a small part of the plot in this particular case.

On the other hand, naming constraints in a temp table is only a recipe for trouble, so it may also be a decision they made while they had the window of opportunity of making a change, without breaking backwards compatibility. Another such change is the fact that all contained databases always the one and same metadata collation.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Sorry, I was working on multiple security inconsistencies at the same time as this one came up.  I've fixed the thread title.

I should have noted that whether it is a good idea or not to create temp tables, constraints on temp tables, or name constraints on temp tables is not the issue.  You can, people ignore recommendations, and people like me have to fix the issues all the time as well as teach people the things you can and shouldn't do.  I ran across this as I was testing another feature and find it to be even more confusing than the way it was before.

Someone may have taken an "opportunity", but all that resulted in even more confusion.  The error message references contained databases, says that you can't create a named constraint, and further states that it is being created in a contained database.  None of which are true.  tempdb is certainly not a contained database and you can't set the containment property on a system database.  So the part saying that it is being created in a contained database is not correct.  Further, the creation of the named constraint is certainly allowed, proven by the fact that all you have to do is change database context to tempdb and the code runs without any errors.  (Yes, if a second copy of the table is attempted to be created an error message will occur, but this has nothing at all to do with a contained database.)

Taking your example, I can create the following procedure in a contained database.

CREATE PROCEDURE bad_sp AS
CREATE TABLE t (a int NOT NULL,
                 CONSTRAINT pk PRIMARY KEY(a))
go

The procedure is created without an error and if someone had executed the procedure before moving the database to another instance and then it gets executed again after moving to a different instance, the second invocation will throw an error message that the object already exists.

I'm sure you've run into the extension of the same overall issue which makes even less sense.  Primary keys are physically implemented as unique indexes (clustered by default).  Running this code is completely valid and returns no errors at all.  The important part is that I have the same name for the unique clustered index on two different tables in the same schema.

create table dbo.test
(Col1   int not null)
go

create table dbo.test1
(col1   int not null)
go

create unique clustered index idx_1 on dbo.test (Col1)
go

create unique clustered index idx_1 on dbo.test1 (Col1)
go

drop table dbo.test
drop table dbo.test1
go

But, if I attempt to implement this same thing using primary key constraints, the second table throws an error for a duplicate name on the constraint.

create table dbo.test
(Col1   int not null,
constraint idx_1 primary key (Col1))
go

create table dbo.test1
(col1   int not null,
constraint idx_1 primary key (Col1))
go

drop table dbo.test
go

Last I checked, a constraint isn't an independent object and neither is an index.  Both of them have to be tied to a table and can't exist without the table.  But the namespace for an index is schema.table.index while the namespace for the constraint is schema.constraint.  This is incredibly confusing, makes no sense, and all the error message preventing you from doing this in tempdb from the context of a contained database just creates even more confusion.

---

Mike Hotek [e-mail]

Answer 3

Someone may have taken an "opportunity", but all that resulted in even more confusion.  The error message references contained databases, says that you can't create a named constraint, and further states that it is being created in a contained database.  None of which are true. 

I need to disagree there.

tempdb may not be contained, but what happens in tempdb still depends on the database from which you are running the command. Take this for instance:

CREATE TABLE #t(a varchar(20) COLLATE database_default)

"database_default" here relates to the current database, not the collation of tempdb. (And in a contained database, you don't have to specify COLLATE database_default; your tempdb columns will have the same columns as your database.)

The error message is slightly misleading. What is disallowed is a CREATE TABLE statement for a temp table with a named constraint in a contained database. This may seem like hair-splitting, but say that you want to run a MERGE statement on a table which is compat level 100. However, you issue the command from a database with compat level 90, so the command fails. That is, what matters is the database where the command is issued.

The error message would possibly make this clearer, if it said "...from a contained database" rather than "in".

I'm sure you've run into the extension of the same overall issue which makes even less sense.  Primary keys are physically implemented as unique indexes (clustered by default).  Running this code is completely valid and returns no errors at all.  The important part is that I have the same name for the unique clustered index on two different tables in the same schema.

Yes, the fact that constraints lives in sys.objects and are objects in their own rights is not a very good design decision. However, it seems unlikely that this will ever change.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

No, it isn't splitting hairs.  But, I'd have to disagree with you.  This error message is not only badly worded, it is completely inconsistent in behavior.  Run the two scenarios below.  These scenarios do the following from a user database for a table with a name constraint:
1. create a local temp table
2. create a "persistent" table in tempdb
3. create a global temp table
4. create an sp that creates a local temp table
5. create an sp that creates a "persistent" table in tempdb
6. create an sp that creates a global temp table

Scenario 1 is with containment turned off and you get the behavior just like it has always been - every statement runs without an error and creates the object specified.  Scenario 2 is with containment on.  This is where it becomes completely inconsistent.  1 and only 1 of the commands fails and that is #1 where the code directly creates a temp table.  Everything else succeeds.  Executing the sp in #4 fails with the same error message as you get from #1, but the other 2 sps execute successfully.  Unfortunately, you can't classify this as a bug, because someone obviously created a block of code in the engine that looks for the direct creation of a local temp table with a named constraint, prevents the code from succeeding, and throws back a very specifically, badly worded error message.

You could also run a 3rd scenario where the database started out with containment off, you created the three sp permutations in the database, changed the containment to partial, and then ran the sps.  You'd get the same results as scenario 2.

If it were coded to have consistent behavior, every one of these statements should fail where run from the context of a contained database.  If I can't create a local temp table with a named constraint from a contained database, I shouldn't be able to create an sp in a contained database that performs this action.  Likewise, I shouldn't be able to create either a global temp table or a "persistent" table in tempdb from a contained database. 

Alternatively, the code that does the check for the local temp table should be removed, so that it behaves as it always has and doesn't break applications upon an upgrade if the containment option is changed.  Because, contained or not, this code will fail if executed a second time while this constraint name exists from another connection.  Moving a contained database to another instance doesn't suddenly have the potential to have this break.  This would break in SQL Server 6.5 through SQL Server 2008 R2 if you were to restore the database to another instance and attempt to run any of the stored procedure permutations listed below when another object has a constraint named pk_test in the tempdb database (or either of the global or "persistent" tables").  I'm not sure for 4.21a or prior Sybase versions, because I can't remember when stored procs were introduced.

Again, I'm not debating whether this is bad practice, a bad idea, poor coding, or anything else.  I stumbled across this when trying to put together a simple demo.  Whether it's a bad practice or not, what has been introduced with error 12827 is a problem.

--Scenario 1 - Containment = NONE
create database test
go
alter database test set containment = none
go
use test
go

create table #test
(ID int not null,
constraint pk_test primary key (ID))
go

drop table #test
go

create table tempdb.dbo.test
(ID int not null,
constraint pk_test primary key (ID))
go

drop table tempdb.dbo.test
go

create table ##test
(ID int not null,
constraint pk_test primary key (ID))
go

drop table ##test
go

create procedure sp_test
as
create table #test
(ID int not null,
constraint pk_test primary key (ID))
go

drop procedure sp_test
go

create procedure sp_test
as
create table tempdb.dbo.test
(ID int not null,
constraint pk_test primary key (ID))
go

drop procedure sp_test
go

create procedure sp_test
as
create table ##test
(ID int not null,
constraint pk_test primary key (ID))
go

drop procedure sp_test
go

use master
go
drop database test
go

--Scenario 2 - Containment = PARTIAL
create database test
go
alter database test set containment = partial
go
use test
go

create table #test
(ID int not null,
constraint pk_test primary key (ID))
go

/*
Msg 12827, Level 16, State 1, Line 1
User-named PRIMARY KEY constraint 'pk_test' is not allowed on temp table '#test' because it is being created in a contained database. Please consult the Books Online topic Understanding Contained Databases for more information on contained databases.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
*/

create table tempdb.dbo.test
(ID int not null,
constraint pk_test primary key (ID))
go

drop table tempdb.dbo.test
go

create table ##test
(ID int not null,
constraint pk_test primary key (ID))
go

drop table ##test
go

create procedure sp_test
as
create table #test
(ID int not null,
constraint pk_test primary key (ID))
go

drop procedure sp_test
go

create procedure sp_test
as
create table tempdb.dbo.test
(ID int not null,
constraint pk_test primary key (ID))
go

drop procedure sp_test
go

create procedure sp_test
as
create table ##test
(ID int not null,
constraint pk_test primary key (ID))
go

drop procedure sp_test
go

use master
go
drop database test
go

---

Mike Hotek [e-mail]

Answer 5

I agree that in the case you create a stored procedure that includs a CREATE TABLE statement with a named constraint for a temp table, you should be slapped on the fingers immediately. Unfortunately, SQL Server is generally fairly lax to what happens at CREATE time, and often defers error checks to run time.

A tricky issue is that in SQL 2012 we only have partial containment, and "partial" could mean whatever you feel like. In a fully contained database, I would expect 2 and 5 to fail, named constraint or not. In a fully contained database, you are not supposed to access any other databases at all. For global temp tables, I don't know, but if you create a global temp table you have a dependency outside the database - someone else could be creating that global temp table.

Then again, they could make other changes like introducing a tempdb/scratch area per database, so it is difficult to look into the crystal ball.

But maybe it was a general cleanup of disallowing something that should not have been permitted in the first place.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

Well, I'm not looking into a crystal ball or speculating on what they might think to do in some unspecified future release.  I'm looking at what they did in this release and the fact that the behavior makes no sense, the error message makes no sense, and the behavior isn't even consistent.  It's like someone threw a dart at a wall and picked off something random to stick into the engine which has no valid reason for being there.

The DATE data type was pulled from SQL Server 2005, because they didn't have the time to make it behave consistently across all functions and data conversions.  The same goes for dozens of other features that were pulled, because there wasn't enough time to make them behave consistently.  Why should I give them a pass on making something that was consistent and with this next release is not only no longer backwards compatible, but has a random permutation out of the 6 possible behaving differently?

---

Mike Hotek [e-mail]

Answer 7

I guess we can agree on disagreeing.

But frankly, I don't understand what you are upset over. Being able to name a constraint in a tempdb is just silly, and there is no backwards compatibility issue here because there are no contained datbases in earlier versions of SQL Server. As I've already mentioned, this is not the only behavioural change between a non-contained and a partially contained database. Look, and you find more - and that are more severe than this one.

---

Erland Sommarskog, SQL Server MVP, [e-mail]