By default, SQL server required the user has login exist at the server level and the user at the database level in order to access the database. From Microsoft SQL Server 2012, the new concept of the database “Contained Database” has been introduced.
Users can connect to the database without authenticating a login at the Database Engine level. Isolating the database from the Database Engine makes it eerier to move the database to another instance.
Contained Databases does not support replication, change tracking , change data capture.
When connect to to the SQL server from SSMS, I get below error 18456. This is because, the login only exist in the contained DB, SQL server does not have knowledge for this login. Therefore, we have to specify the database in the connection.
After successfully connect, the user only can see contained DB.
Move Contained database
Use the normal backup restore database procedure . The target instance must also enable the contained database on the server instance level.
Covert Database to contained database
Create the test user
After convention, we can se the user login ’skye2’ authentication type has changed from ‘INSTANCE’ to ‘DATABASE’.
Error: 18456, Severity: 14, State: 58.
While testing with contained database, I get this error when use the contained user to login.
2014-09-28 18:50:23.95 Logon Error: 18456, Severity: 14, State: 58.
2014-09-28 18:50:23.95 Logon Login failed for user 'skye1'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 192.168.116.1]
The error is pretty self explain. To resolved this error, we have to enable the authentication mode to mixed. See here for more detail.
ERROR Msg 5061, Level 16, State 1, Line 24
While convert the database, to PARTIAL or NONE. We need to get the exclusive lock on the database level. No user can access it. If there are existing user connection to the database, we will get the below error.
Msg 5061, Level 16, State 1, Line 24
ALTER DATABASE failed because a lock could not be placed on database 'NorthWind'. Try again later.
Msg 5069, Level 16, State 1, Line 24
ALTER DATABASE statement failed.