初探SQL Server 2012 包含数据库

SQL Server将认证和授权分散给了不同的对象来完成。SQL Server的“登录名”用于认证，连接SQL Server的Windows账号或账号所在的组必须在SQL Server中有对应的登录名才能成功登录到SQL Server上。而每个数据库中的“用户”被授予了操作数据库中对象的相应权限

SQL Server将认证和授权分散给了不同的对象来完成。SQL Server的“登录名”用于认证，连接SQL Server的Windows账号或账号所在的组必须在SQL Server中有对应的登录名才能成功登录到SQL Server上。而每个数据库中的“用户”被授予了操作数据库中对象的相应权限。登录名和用户之间通过SID联系起来。每个登录名在master数据库中都记录有该登录名所对应的SID。而在用户数据库中，用户名也会和某个SID对应起来。通过SID的连接，登录名被映射到了数据库用户上，于是登录SQL Server的Windows账号也获得了操作数据库的相应权限。 

今天看来这个机制带来了如下两个问题：

1.       提高了高可用解决方案的维护成本。举例来说，对于一个启用了数据库镜像的系统，如果你在主体服务器上添加了新的登录名来访问镜像数据库的话，你必须在镜像服务器上也添加相同的登录名，否则一旦发生了故障转移，数据库就无法使用新的登录名进行访问。另外，在镜像服务器上添加登录名时要确保和主体服务器上的登录名使用相同的SID。否则就会破坏登录名到数据库用户之间的对应关系，成为所谓的孤立用户。

迁移Login可以参考：How to transfer logins and passwords between instances of SQL Serverhttp://support.microsoft.com/kb/246133 

2.       增加了迁移应用的复杂程度。应用程序所需要的数据都保存在用户数据库中，但是当你需要将应用从开发坏境迁移到生产环境，或者从老的服务器迁移到新的服务器上的时候，你不能仅仅简单的迁移用户数据库和程序。因为还有一部分和应用相关的数据遗漏在用户数据库之外，其中包括登录名。在迁移应用的时候，登录名需要被单独的从老的环境中提取出来，再部署到新环境上。

为了解决这样的问题，SQL Server 2012引入了新的特性，叫做Contained Database认证。Contained Database认证主要是由两类对象构成整个认证体系，一个叫做Contained Database。另一个是创建在Contained Database中的用户对象，被称为Contained database user。Contained database，顾名思义，就是将应用所需要的所有信息全部“包含”在用户数据库内部，不遗漏在系统数据库中。当应用程序连接contained database的时候，它不需要通过记录在master数据库中的登录名然后再映射到数据库用户，而是直接在contained database上进行验证，然后获得对应的contained database user被授予的权限进行数据库操作。可以说Contained database user就是一个把传统的登录名和数据库用户名的功能揉在一起的一种新的对象类型。有个Contained Database认证，上面谈到的两个问题就能很简单的得到解决。

Contained Database认证并不是独立于传统的Windows认证和SQL认证之外的新的认证方式，而是建立在这两种认证方式之上的新功能。也就是说，登录到SQL Server的账号依旧需要通过Windows认证或SQL Server认证的机制来验明正身，只是一旦验证通过该账号就会直接被转换成一个数据库用户。因此你根本不需要在实例中为该账户建立任何对应的登录名。

MakeSong

AI音乐生成，生成高质量音乐，仅需30秒的时间

145

查看详情

--在SQL Server上 enable containeddatabase认证

EXECsp_configure'show advanced options', 1;

GO

RECONFIGUREWITHOVERRIDE;

GO

EXECsp_configure'contained databaseauthentication', 1;

EXECsp_configure'show advanced options', 0;

GO

RECONFIGUREWITHOVERRIDE;

go

--创建包含数据库 [ContainedDatabase]

USE[master]

GO

CREATEDATABASE[ContainedDatabase]CONTAINMENT=PARTIAL COLLATELatin1_General_100_CS_AS

GO

---创建包含数据库用户

USE[ContainedDatabase]

GO

CREATEUSER[test]WITHPASSWORD=N‘test’

GO

用创建的数据库用户登陆时需要注意要选择连接到Contained数据库（DBConnection也要指定，否则无法登陆数据库）。

连接后只能看到包含数据库：

包含用户属性：

验证过程（图片来自于http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/08/contained-database-authentication-in-depth.aspx）：

Contained Database除了能够使得认证过程“跳过”登录名这个对象之外，还带来了一些其他的好处。比如说，使用了Contained Database，你就不用担心数据库的collation和系统数据库的collation不一致的问题了。当你对于Contained database进行的SQL操作时，如果产生了临时表的话，临时表的collation全部是和Contained database的collation一致，而不是和tempdb一致。

创建一个数据库排序规则为Latin1_General_100_CS_AS（系统数据库排序规则为Chinese_PRC_CI_AS

）

运行下面的语句我们会遇到排序规则冲突问题：

createdatabasetesta COLLATELatin1_General_100_CS_AS

GO

USEtesta;

GO

CREATETABLEdbo.test

(

    bar NVARCHAR(32)

);

CREATETABLE#test

(

    bar NVARCHAR(32)

);

GO

SELECT*

    FROM #testASa

    INNER JOINdbo.testASb

    ON a.bar=b.bar;

GO

Msg 468, Level 16, State 9, Line 4

Cannot resolve the collation conflict between"Latin1_General_100_CS_AS" and "Chinese_PRC_CI_AS" in theequal to operation.

在Contained数据库上运行成功：

但是Contained数据也是有一些限制的，所以使用前要做评估：

部分包含数据库不允许以下功能。

·       部分包含数据库不能使用复制、更改数据捕获或更改跟踪。

·       编号过程

·       绑定到架构的对象，且依赖于可更改排序规则的内置功能

·       绑定因排序规则更改而导致的变化，包括对对象、列、符号或类型的引用。

·       复制、变更数据捕获和更改跟踪。

使用sys.dm_db_uncontained_entities和sys.sql_modules(Transact-SQL)视图可返回有关非包含对象或功能的信息

备注：

用户实体分为以下几种包含类别：

·       完全包含的用户实体（从不跨越数据库边界的用户实体），例如 sys.indexes。任何使用这些功能的代码或任何只引用这些实体的对象也都是完全包含实体。

·       非包含用户实体（跨越数据库边界的用户实体），例如 sys.server_principals或服务器主体（登录名）本身。任何使用这些实体的代码或任何引用这些实体的功能都是非包含实体。