contained databases

Question

contained databases

lkennedy76

SSChampion

Points: 13683
More actions
October 17, 2013 at 7:08 am

#278180

I have a database that was setup to use containment type partial. We are want ing to make this database containment type none. Does anyone have step by step instruction on how to do this.
I ran sp_configure for the engine to change the containment type to = 0 and then ran a script to place DB in single user mode, alter database containment type = none, multi user mode but it would not take. At one point it looked to have switched it to none but went back to partial. I have place a call with MS but was reaching out to you guys as well.
Thanks!
MCSE SQL Server 2012\2014\2016

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

You should run the sp_configure last not first.

If you want to disable containment at the instance level then for each database that has containment enabled do the following

alter database [yourdb] set containment = none

The database engine will need exclusive access to the database to remove containment. Once all databases have containment removed you may then run sp_configure to swtch off the instance option.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

lkennedy76 SSChampion Points: 13683 More actions · Answer 2

so I would run this;

USE (mydatabase)

GO

ALTER DATABASE (mydatabase)

SET SINGLE_USER;

GO

ALTER DATABASE (mydatabase) SET CONTAINMENT = NONE

GO

ALTER DATABASE (mydatabase)

SET MULTI_USER;

GO

USE MASTER

GO

SP_CONFIGURE 'CONTAINED DATABSE AUTHENTICATION', 0;

MCSE SQL Server 2012\2014\2016

jonysuise Ten Centuries Points: 1206 More actions · Answer 3

be careful switching the database to single_user if sql server agent is running with sysadmin privileges it will get the only active connection available.

to switch off the containement database feature at your level instance :

sp_configure 'contained database authentication', 0

GO

RECONFIGURE;

GO

Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012

lkennedy76 SSChampion Points: 13683 More actions · Answer 4

so what I have gathered so far;

1. stop sql agent (thanks I totally forgot)

2. RUn script as seen below;

USE (mydatabase)

GO

ALTER DATABASE (mydatabase)

SET SINGLE_USER;

GO

ALTER DATABASE (mydatabase) SET CONTAINMENT = NONE

GO

ALTER DATABASE (mydatabase)

SET MULTI_USER;

GO

USE MASTER

GO

SP_CONFIGURE 'CONTAINED DATABSE AUTHENTICATION', 0;

GO

RECONFIGURE;

MCSE SQL Server 2012\2014\2016

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 5

That should work. However a contained db shouldn't cause you any issues. It should function as non-contained in most situations.

lkennedy76 SSChampion Points: 13683 More actions · Answer 6

Steve Jones - SSC Editor (10/17/2013)
That should work. However a contained db shouldn't cause you any issues. It should function as non-contained in most situations.

Steve,

??? on the second part. We are not having issues using contained however we want to go back to non-contained.

MCSE SQL Server 2012\2014\2016

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 7

The only things that a contained db does in 2012, AFAIK, is

1 - allow logins to the database and not the instance.

2 - ensure that temp tables map to the correct collation of the database

If you don't have sql users w/o login, then #1 doesn't apply and can't happen.

#2 shouldn't matter unless you want collation mismatch errors. If you are uncontained, these can happen.

lkennedy76 SSChampion Points: 13683 More actions · Answer 8

Well to make a bad story sound meh, I was pushed into AlwaysOn \ contain and it was the wrong thing to do, I tried to fight it but alas who really listens to the DBA!

Thanks for the reply! 😀

MCSE SQL Server 2012\2014\2016

Perry Whittle SSC Guru Points: 234013 More actions · Answer 9

Dont bother with SQL agent or anything else, just use the following all within the same session

[Code="sql"]USE (mydatabase)

GO

ALTER DATABASE (mydatabase)

SET SINGLE_USER with rollback immediate;

GO

ALTER DATABASE (mydatabase) SET CONTAINMENT = NONE

GO

ALTER DATABASE (mydatabase)

SET MULTI_USER;

GO

USE MASTER

GO

SP_CONFIGURE 'CONTAINED DATABSE AUTHENTICATION', '0';[/code]

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

lkennedy76 SSChampion Points: 13683 More actions · Answer 10

Thank you Perry, I also have MS involved and they could not get the DB switched over to NONE either. I will email results once I have them.

MCSE SQL Server 2012\2014\2016

Perry Whittle SSC Guru Points: 234013 More actions · Answer 11

It's the Database!!! (10/18/2013)
Thank you Perry, I also have MS involved and they could not get the DB switched over to NONE either. I will email results once I have them.

Does the alter database query fail, if so what error is produced?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

lkennedy76 SSChampion Points: 13683 More actions · Answer 12

Got an answer from MS;

Drop the users in the contained database first, then run the database alter statement, then run the statement on the engine to say 0 contained databases. They found this strange but this is the work around.

MCSE SQL Server 2012\2014\2016