SQL Server 2012新功能:数据库身份验证-技术开发专区

SQL Server 2012新功能:数据库身份验证

作者：孙耀堃译编辑：王玉圆 2012-04-07 00:05 IT168网站原创

【IT168 专稿】将您的数据从一个SQL Server实例移动到另一个实例，并不是一个少见的需求，可能是为了数据迁移，也可能是为了故障转移。问题是，有时需要些驻留在数据库外的数据，所以需要更多的开销而不仅仅是移动数据到一个新的实例;这意味着需要移动/恢复外部数据(比如登录数据等)到你的一个实例。听起来并不困难，但必须强调这是非常繁琐的并且出现任何失误哪怕是一个对象(存储于数据库外部的对象)都会影响你的应用按照预期执行。

　　SQL Server 2012(名称叫Denali)有一个叫做Contained Database Authentication的新功能，解决了上述问题并且为数据库管理者和应用程序开发者提供便利。

　　Contained Database Authentication(包含的数据库身份验证)

　　SQL Server 2012引入了一个叫做Contained Database Authentication的新功能，这个功能允许数据库部分包含存储在外部的数据。换句话说，一旦你在SQL Server实例级启用此功能，你能够建立一个用于存放用户信息和资格的数据库(而不是在主数据库中获取注册信息)，所以当数据库迁移时不需要在目标实例上创建注册信息(做SID的映射)。令人欣慰。

　　除了上述好处，contained database提供了一个更好的功能。当你的服务排序规则和数据库不同时，临时对象将被contained database使用的而不是从服务器引出的排序规则所建立。之前必须使用COLLATE，现在这不是必须的了。

　　请注意：

　　·这适用于那些访问数据库但是在实例级不做任何系统管理的用户。

　　·部分包含的数据库无法使用诸如复制，数据变更捕获或变更跟踪等功能。

　　·部分包含的数据库中创建的用户对于主数据库和临时数据库有guest权限。

　　·此功能用于AlwaysOn以便更便于用户数据迁移。

　　·现在已有足够的理论，让我们围绕这一新功能做一些实际的应用。

　　开始Contained Database Authentication实例

　　SQL Server实例默认情况下不启用Contained Database Authentication。首先，你需要启用它，然后就可以创建with containment的数据库或是可以验证用户的数据库。有两种方法来启用此功能(不是使用PowerShell命令)。

　　第一种方法，使用sp_configure系统存储过程开启，脚本如下：

USE master
GO
sp_configure 'show   advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'CONTAINED   DATABASE AUTHENTICATION', 1
GO
RECONFIGURE
GO
sp_configure 'show   advanced options', 0
GO
RECONFIGURE
GO

SQL Server 2012新功能:数据库身份验证
▲图1-使用脚本配置contained database authentication

　　第二种方法，使用SSMS向导(SQL Server Management Studio)。连接到想开启此功能的SQL Server实例上，在对象资源管理器中右击实例名称，单击“属性”菜单，单击如下所显示的“高级”页面，最后设置“Enable Contained Databases”的值为True，其默认值为False：

SQL Server 2012新功能:数据库身份验证
▲图2-使用向导配置contained database authentication

　　一旦在实例级中启用此功能，你可以在在CREATE DATABASE命令中使用CONTAINMENT = PARTIAL从句创建数据库，命令如下所示。如果没有指定，CONTAINMENT = NONE为默认值。

USE master
GO
--Contained Database   Authentication
CREATE DATABASE   [CDA]
CONTAINMENT = PARTIAL
ON    PRIMARY
(
       NAME   = N'CDA',
       FILENAME   = N'C:\Program   Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\CDA.mdf' ,
       SIZE   = 4096KB ,
       FILEGROWTH   = 1024KB )
LOG   ON
(
       NAME   = N'CDA_log',
       FILENAME   = N'C:\Program Files\Microsoft   SQL Server\MSSQL11.SQL2012\MSSQL\DATA\CDA_log.ldf' ,
       SIZE   = 1024KB ,
       FILEGROWTH   = 10%)
GO
USE [CDA]
GO
IF NOT   EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY')
       ALTER   DATABASE [CDA]   MODIFY FILEGROUP   [PRIMARY] DEFAULT
GO

${PageNumber}

　　创建containment功能数据库不仅可以使用如上所示的脚本，同样可以使用SSMS的“New Database”向导。当使用SSMS向导创建数据库时，你需要将“Containment type”的值从其默认的“None”设置为“Partial”，如下所示：

Contained Database Authentication
▲图3-建立Partial Containment数据库

　　一旦建立了Partial Containment数据库，便可以在其中创建用户，最终将被此数据库认证而不是被SQL Server实例认证。用户可以是一个Windows用户，你只需要指定Windows用户的名称并给予用户所属用户组相应的权限以便他/她能够执行连接到数据库的操作。

USE [CDA]
GO
CREATE USER   [ARSHAD-PC\ARSHAD-WA]
GO
ALTER ROLE   [db_datareader] ADD   MEMBER [ARSHAD-PC\ARSHAD-WA]
ALTER ROLE   [db_datawriter] ADD   MEMBER [ARSHAD-PC\ARSHAD-WA]
--ALTER ROLE   [db_ddladmin] ADD MEMBER [ARSHAD-PC\ARSHAD-WA]
GO

　　用户也可以是下面所说的SQL用户。在这种情况下，需要指定用于连接到数据库的用户名和密码，并且需要给予用户对数据库执行操作的权限。例如，在下面的脚本中我将把用户加入到db_datareader和db_datawriter里面，以使用户可以对数据进行读写操作或使用DDL命令(Data Manipulation Language)。

USE [CDA]
GO
CREATE USER   [ARSHAD-SA] WITH   PASSWORD=N'asdf@1234',
DEFAULT_LANGUAGE=[English],
DEFAULT_SCHEMA=[dbo]
GO

ALTER ROLE   [db_datareader] ADD   MEMBER [ARSHAD-SA]
ALTER ROLE   [db_datawriter] ADD   MEMBER [ARSHAD-SA]
--ALTER ROLE   [db_ddladmin] ADD MEMBER [ARSHAD-SA]
GO

　　现在使用上面创建的账户连接数据库，并通过验证。

Contained DatabaseAuthentication实例
▲图4-连接数据库使用contained database authentication-1

　　这是可以设想出来的，因为即使用户对数据库进行访问，数据库验证默认下是不起作用的。原因在于你要指定想要建立连接的数据库名称并通知数据库对用户进行验证;因此，打开如下所示的Connection Properties页面，指定数据库名称后再次连接，可以达到预期效果。

Contained DatabaseAuthentication实例
▲图5-连接数据库使用contained database authentication-2

${PageNumber}

　　一旦通过验证并且连接到数据库，用户将只能看到他/她所连接到得数据库，但是如果用户通过SQL实例验证，其他实例级选项也是可见的，如下所示：

Contained DatabaseAuthentication实例
▲图6-资源管理视图

Contained DatabaseAuthentication实例
▲图7-通过实例级验证的资源管理视图

　　现在对数据库进行操作，数据库验证的用户只执行数据库内的操作;不允许进行实例级上的操作，甚至对数据库备份也不可以。正如你所看到的，当右击时没有备份选项和还原数据库选项，如下所示。

Contained DatabaseAuthentication实例
▲图8-不允许外部数据库操作

　　如果你记得我们仅给予了读写权限，如果用户试图执行DDL(Data Definition Language)建立对象，将会异常导致失败：

USE [CDA]
GO
CREATE TABLE   Employee
(
       [EmployeeID]   INT IDENTITY,
       [FirstName]   Varchar(100),
       [LastName]   Varchar(100),
       [Address]   Varchar(100),
)
GO

运行实例及总结
▲图9-用户权限足够，可以创建数据库对象-1

运行实例及总结
▲图10-用户权限足够，可以创建数据库对象-2

　　如果你想使用户创建数据库对象，你需要让用户具有db_ddladmin或db_owner角色，否则创建表时会发生异常。

　　数据库验证用户已经具有读/写权限;他/她使用如下脚本不会出现问题。

USE [CDA]
GO
INSERT INTO   Employee([FirstName], [LastName], [Address])
VALUES ('Arshad', 'Ali', 'India')
INSERT INTO   Employee([FirstName], [LastName], [Address])
VALUES ('John', 'Mathew', 'India')
GO
SELECT *   FROM Employee
GO

　　总结

　　本篇文章中，我谈到了一个名为Contained Database Authentication的新功能。它允许数据库包含之前存放在外部的数据，比如登录信息等，并且消除了迁移数据所产生的额外开销。

关注我们