SQLServer 2012 包含数据库（Contained Databases）

“包含数据库”是独立于其他数据库以及承载数据库的 SQL Server 实例的一种数据库。 SQL Server 2012 以 4 种方法帮助用户使其数据库独立于实例。


▶很多用于描述数据库的元数据都在该数据库中维护。 （除此之外或代替在 master 数据库中维护元数据。）
▶使用相同的排序规则定义所有元数据。
▶数据库可执行用户身份验证，因此减少了对 SQL Server 实例的登录名的数据库依赖关系。
▶SQL Server 环境（DMV、XEvent 等）报告并可以执行包含信息。


相关概念：（更多参考：包含数据库）
数据库边界：数据库和 SQL Server 实例之间的边界。 数据库和其他数据库之间的边界。
包含：完全在数据库边界中存在的元素。
非包含：跨数据库边界的元素。
非包含数据库：具有设置为 NONE 的包含的数据库。 版本早于 SQL Server 2012 的所有数据库均属于非包含数据库。 默认情况下，所有 SQL Server 2012 数据库的包含都设置为 NONE。
部分包含数据库：部分包含数据库是一种包含数据库，可允许存在跨越数据库边界的某些功能。 SQL Server 包括确定何时跨越包含边界的功能。
包含的用户：包含数据库有两种用户类型。


以下测试包含数据库中的 对象、用户、排序规则是怎么独立的！

【初始环境】

--创建数据库 DROP DATABASE DemoDatabase
USE [master]
GO
CREATE DATABASE DemoDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

--创建登录账户和数据库用户[UserA]
USE [master]
GO
CREATE LOGIN [UserA] WITH PASSWORD=N'UserA', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DemoDatabase]
GO
CREATE USER [UserA] FOR LOGIN [UserA]
GO
ALTER ROLE [db_owner] ADD MEMBER [UserA]
GO

--只创建登录账户[UserB]
USE [master]
GO
CREATE LOGIN [UserB] WITH PASSWORD=N'UserB', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
--创建Windows用户和SQLserver登录账户[UserB]
CREATE LOGIN [KK-PC\WindowsUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

--先在数据库创建表
USE [DemoDatabase]
GO
SELECT * INTO [DemoTable] FROM sys.sysobjects
GO
CREATE VIEW [VDemoTable]
AS 
SELECT * FROM [DemoDatabase].[DBO].[DemoTable]
GO

【将数据库 [DemoDatabase] 设置为部分包含数据库】

USE [master]
GO
EXEC sys.sp_configure N'show advanced', N'1'; 
 GO
RECONFIGURE WITH OVERRIDE;  
GO
EXEC sys.sp_configure N'contained database authentication', N'1';
GO
RECONFIGURE WITH OVERRIDE;
GO
ALTER DATABASE [DemoDatabase] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO

【创建独立的数据库用户】

这里有四种登录用户，就测试四种情况（不包括加密用户）

【一】创建有密码的数据库用户（不需要创建登录账号）

--【一】创建有密码的数据库用户（不需要创建登录账号）
USE [DemoDatabase]
GO
CREATE USER [UserC] WITH PASSWORD=N'UserC', DEFAULT_LANGUAGE=[English], DEFAULT_SCHEMA=[dbo]	
GO
ALTER ROLE [db_owner] ADD MEMBER [UserC]
GO

连接数据库：

可以看到，当数据库的用户密码都包含在数据库中时，就不需要外部登录账号做映射了，直接可以访问数据库！切只能访问数据库中的资源，更好地控制了权限。

【二】从登录账号中创建数据库用户,与以前的添加一样：

--【二】从登录账号中创建数据库用户,与以前的添加一样。
USE [DemoDatabase]
GO
CREATE USER [UserB] FOR LOGIN [UserB] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [UserB]
GO

该用户 [UserB] 需要登录账号做映射，属于边界外的对象。这种情况与非包含的数据库一样。

【三】从window创建数据库用户(这种添加用户的情况和上一种一样)

--【三】从window创建数据库用户(这种添加用户的情况和上一种一样)
USE [DemoDatabase]
GO
CREATE USER [KK-PC\WindowsUser] FOR LOGIN [KK-PC\WindowsUser] WITH DEFAULT_LANGUAGE=[English], DEFAULT_SCHEMA=[dbo]	
GO
ALTER ROLE [db_owner] ADD MEMBER [KK-PC\WindowsUser]
GO

【四】添加无密码的数据库用户(与以前版本一样，因为特殊顺便说明这种情况用法吧)

--【四】添加无密码的数据库用户(与以前版本一样，因为特殊顺便说明这种情况用法吧)
USE [DemoDatabase]
GO
CREATE USER [UserD] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]	
GO
ALTER ROLE [db_owner] ADD MEMBER [UserD]
GO
--用Windows账号测试，先回收其权限
ALTER ROLE [db_owner] DROP MEMBER [KK-PC\WindowsUser]
GO

--使用 Windows 账号[KK-PC\WindowsUser]登录数据库并查询表
USE [DemoDatabase]
GO
SELECT * FROM [dbo].[DemoTable]
GO

错误，并没有权限使用表

--回到管理员窗口，授予登录账号[KK-PC\WindowsUser]使用数据库用户[UserD]的凭据
USE [DemoDatabase]
GO
GRANT IMPERSONATE ON USER::[UserD] TO [KK-PC\WindowsUser];
GO 

--回到Windows账号[KK-PC\WindowsUser]的登录界面，切换用户执行语句！
USE [DemoDatabase]
GO
EXECUTE AS USER = 'UserD' ;
GO
SELECT USER_NAME()
SELECT * FROM [dbo].[DemoTable]
GO
REVERT ;
GO

结果可以查询了！数据库用户[UserD]虽然没有登录账号映射，但是可以授权给其他登录账号使用。

以上4种情况，主要是第一种才体现出了包含数据库的特性。

【相关查询】

USE [DemoDatabase]
GO

--显示数据库中使用的任何非包含对象
SELECT * FROM sys.dm_db_uncontained_entities
SELECT * FROM sys.sql_modules

--边界外的表等对象
SELECT t1.name,t1.type,t0.*
FROM sys.dm_db_uncontained_entities t0 
inner join sys.objects t1 on t0.major_id=t1.object_id and t0.class=1

--边界外的用户
SELECT t1.name,t1.type,t0.*
FROM sys.dm_db_uncontained_entities t0 
inner join sys.database_principals t1 on t0.major_id=t1.principal_id and t0.class=4

【将边界外的对象更改为边界内对象】

--对于视图，将引用的数据库名称去掉
USE [DemoDatabase]
GO
ALTER VIEW [VDemoTable]
AS 
SELECT * FROM [DBO].[DemoTable]
GO

--对于用户（存在登录账户和数据库用户）,禁用登录账号并中断映射。
USE [DemoDatabase]
GO
EXEC sp_migrate_user_to_contained
@username = N'UserA',   
@rename = N'keep_name',   
@disable_login = N'disable_login'  
GO
EXEC sp_migrate_user_to_contained
@username = N'UserB',   
@rename = N'keep_name',   
@disable_login = N'disable_login'  
GO

【排序规则问题】

查看实例和数据库的排序规则，现在是不一样的。

USE [DemoDatabase]
GO
--	DROP TABLE T1,#T2
CREATE TABLE T1 (T1_txt nvarchar(max)) ;
GO
CREATE TABLE #T2 (T2_txt nvarchar(max));
GO

SELECT T1_txt FROM T1
UNION ALL
SELECT T2_txt FROM #T2

SELECT T1_txt, T2_txt FROM T1 JOIN #T2 ON T1.T1_txt = #T2.T2_txt ;

执行结果在包含数据库中并没有出错！现在在非包含数据库中执行，会怎样呢？？

USE [AdventureWorks2012]
GO
--	DROP TABLE T1,#T2
CREATE TABLE T1 (T1_txt nvarchar(max)) ;
GO
CREATE TABLE #T2 (T2_txt nvarchar(max));
GO

SELECT T1_txt FROM T1
UNION ALL
SELECT T2_txt FROM #T2

SELECT T1_txt, T2_txt FROM T1 JOIN #T2 ON T1.T1_txt = #T2.T2_txt ;

Msg 451, Level 16, State 1, Line 1
Cannot resolve collation conflict between "Chinese_PRC_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in UNION ALL operator occurring in SELECT statement column 1.

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

结果出错！因排序规则不同导致错误！默认情况下，数据库排序规则等同于 model 数据库排序规则。而临时数据库 tempdb 默认为实例的排序规则。由于包含数据库的设计目标是让自身实现独立，因此必须切断它们对实例和 tempdb 排序规则的依赖。 为此，包含数据库引入了目录排序规则的概念。 目录排序规则适用于系统元数据和临时对象.

--当然也可以使用 COLLATE 指定排序规则来兼容。
USE [AdventureWorks2012]
GO
SELECT T1_txt COLLATE Chinese_PRC_CI_AS FROM T1 
UNION ALL
SELECT T2_txt FROM #T2

SELECT T1_txt, T2_txt FROM T1 JOIN #T2 ON T1.T1_txt COLLATE Chinese_PRC_CI_AS = #T2.T2_txt ;

更多参考：

包含数据库: https://msdn.microsoft.com/zh-cn/library/ff929071(SQL.110).aspx
包含数据库的排序规则: https://msdn.microsoft.com/zh-cn/library/ff929080(v=sql.110).aspx
sys.dm_db_uncontained_entities: https://msdn.microsoft.com/zh-cn/library/ff929336(v=sql.110).aspx