A couple of important concepts on contained database

1. Contained login is the same thing as contained user. You use the CREATE USER syntax to create a contained login (see p.85 on the book "Securing SQL Server: Protecting Your Database from Attackers"  By Denny Cherry).




2. In SQL Server 2012, no fully contained database, a database is either NONE or partially contained.

from BOL:
CREATE DATABASE database_name 
[ CONTAINMENT = { NONE | PARTIAL } ]
[ ON 
      [ PRIMARY ] <filespec> [ ,...n ] 
      [ , <filegroup> [ ,...n ] ] 
      [ LOG ON <filespec> [ ,...n ] ] 
] 
[ COLLATE collation_name ]
[ WITH  <option> [,...n ] ]
[;]
 
CONTAINMENT

Specifies the containment status of the database. NONE = non-contained database. PARTIAL = partially contained database.

In SQL Server 2012 the contained database feature is currently available only in a partially contained state. A partially contained database is a contained database that allows the use of uncontained features.

How to assign appropriate permissions for different groups of users in SQL Server if the built-in server roles do not meet your need?

The nine server roles in SQL Server 2005/08/R2 are fixed and inflexible. If they are not sufficient to meet your needs, you can use the flexible SQL Server login and user-defined database role. For instance, you can create a "Developer" database role for a database in SQL 2008. The role should be allowed to read anything, and view the definitions of SPs/Functions, etc; but you do not want them to be able to alter SPs/Tables, etc.


Use [iDBA]
GO

--Step 1 - create a SQL Server login
Create LOGIN [ClarkKent] WITH PASSWORD = 'NotTheRealPassword'

--Step 2 - create a Database user for the login
Create USER [ClarkKent] FOR LOGIN [ClarkKent]

--Step 3: Create a user-defined database role
CREATE ROLE [MyDevelopers]

--Step 4 - Add the user-defined database role to the fixed database role
--allow the users to read data
EXEC sp_addrolemember   N'db_datareader',
                                    N'MyDevelopers'

--Step 5 - assign additional permissions to the database role
--allow the users to see view proc and function definitions
Grant View Definition ON SCHEMA :: [dbo] To [MyDevelopers]

-- Step 6: finally add the user to the role:
EXEC sp_addrolemember   N'MyDevelopers',
                                    N'ClarkKent'

--step 7: test:
EXECUTE AS USER = 'ClarkKent'
--who am i?

SELECT
      SUSER_NAME()
--do stuff

--change back into superman
REVERT;

--clean up after ourselves
/*
DROP ROLE [MyDevelopers]
DROP USER [ClarkKent]
DROP LOGIN [ClarkKent]
*/


see http://www.sqlservercentral.com/Forums/Topic1259555-1526-1.aspx#bm1259574 for the original post.