Partially Contained Database – Part 2: Create database, convert non partially contained database and migrate users

Now that we enabled the instance for contained databases, see my post Partially Contained Database – Part 1: Setup, we are ready to create a partially contained database (Partial-CDB) and convert an existing non contained database to a Partial-CDB.

1. Create a Partial-CDB

Create a new user database like you normally do either by using the GUI or Transact-SQL. Notice the Containment type and set it to Partial. There is no fully containment yet in SQL Server 2012. You will have to wait for the next version or migrate to SQL Azure.

Or, doing it using T-SQL:

CREATE DATABASE [PartialCDB] CONTAINMENT = PARTIAL
GO

2. Create a user using the contained database authentication

Partial-CDB lets us choose between a SQL user with password or a Windows user. Let’s go for a SQL user with password as it is specific to a partial-CDB. Notice here that our new user is not linked to a server login which makes it not dependent on the instance. That’s it! All  you have to do is create a user with a password and it’s contained within the database.

Or using T-SQL:

USE [PartialCDB]
GO
CREATE USER [Giselle] WITH PASSWORD=N'xxx'
GO

3. Migrating users

Converting a non-partially contained database to a partially contained database does not mean that the existing database users are automatically migrated as well.

Before we migrate the user(s), let’s have a look at the authentication type of the user(s):

select name, type_desc, authentication_type, authentication_type_desc
from sys.database_principals
where name = 'giselle'

Result:

There is a system extended stored procedure sp_migrate_user_to_contained that takes care of the migration for us.
We will migrate all users using a cursor. I know, we’re not supposed to use cursors but I think it can be done for small administration tasks.

declare @user sysname
declare user_cursor cursor
for select dp.name
from sys.database_principals dp
inner join sys.server_principals sp
on dp.sid = sp.sid
where dp.authentication_type = 1
and sp.is_disabled = 0

open user_cursor
fetch next from user_cursor into @user
while @@FETCH_STATUS = 0
begin
execute sp_migrate_user_to_contained
@username = @user,
@rename = N'keep_name',
@disablelogin = N'disable_login';
fetch next from user_cursor into @user
end
close user_cursor
deallocate user_cursor

Now that the migration is done, let’s check:

select name, type_desc, authentication_type, authentication_type_desc
from sys.database_principals
where name = 'giselle'

Result:

We see that the authentication type has changed from 1 tot 2.

That’s all there is to it!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>