本文属于SQL Server安全专题系列
接上文http://blog.csdn.net/dba_huangzj/article/details/79030680实例级别安全性包含创建和管理登录名、凭证（credentials）和服务器角色。相对的安全主体在实例级别包含数据库、端点和AlwaysON Availability Groups。本文将讨论登录名、服务器角色和凭证。
登录名：
从SQL Server 2012开始，在包含数据库中，可以让用户直接连到数据库，而不需要在实例层面验证。SQL Server支持两种实例级别验证模式：
Windows 身份验证：只勾选这个时，用户只能使用Windows本地账号或者域账号去连接SQL Server。SQL Server的账号与这个Windows账号关联，Windows主体的SID（security identifier）会存储在master库中。
混合身份验证：也称SQL Server和Windows 身份验证模式，见下图，在这种模式下，Windows身份验证依旧生效。同时可以创建SQL 登录名，这种登录名具有名字、密码和SID，并存储在master库中。我们最常用的莫过于sa。使用这种方式，用户可以不具有任何Windows权限，即可登录到SQL Server。
混合身份验证比Windows身份验证略微不安全，因为可以直接绕过Windows域的保护，只要直到账号密码即可直接登录，同时在数据库审计层面也不方便（假设所有人都用sa登录，出了问题很难查找谁做的）。所以最好使用Windows身份验证。在一些情况下，可以考虑使用混合身份验证：
- 遗留应用系统中需要。
- 从域外访问，如从Linux服务器访问。但是在SQL On Linux版本中，这个限制则不是非常必要。
- 安全审计要求。
创建登录名：
Windows身份验证：
在使用T-SQL命令创建登录名时，如果创建Windows身份验证，则语法很直接，因为不需要输入密码。必要重要的WITH选项有两个：
- DEFAULT_DATABASE：代表这个登录名登录成功后的默认打开的数据库。个人习惯一般是DBA为tempdb，开发人员直接指向开发库，默认是master，过去本人经常写错代码在master库，如果是批量代码，删起来就很恶心。
- DEFAULT_LANGUAGE：如果不指定登录名的默认语言，那么会使用实例上的默认语言，语言看似不重要，但是在特别是日期格式的使用过程有非常严重的影响，并且不容易发现。作为良好的管理，应该制定统一的规范
Windows身份验证的登录名创建案例：
创建一个登录名，使用域名\账号名格式：cartersecuresafe\Pete。默认数据库为master，默认语言为英式英语。
USE master
GO
CREATE LOGIN [cartersecuresafe\Pete]
FROM WINDOWS
WITH DEFAULT_DATABASE=master, DEFAULT_LANGUAGE=British ;
GO SQL Server身份验证：
- PASSWORD：指定原始密码，以明文显示。
- PASSWORD HASHED ：指定登录所使用的初始密码的哈希表示形式。
- SID：指定登录名的SID。
- DEFAULT_DATABASE/DEFAULT_LANGUAGE ：与Windows身份验证一样。
- CHECK_POLICY：指定登录的密码必须符合组策略或本地安全策略中强制执行的 Windows 用户的相同要求 (如长度和复杂性)。
- CHECK_EXPIRATION：指定登录的密码将按照组策略或本地安全策略强制执行的 Windows 用户密码过期策略的规定过期。仅在指定了 CHECK_POLICY,时这个选项才有效。
- MUST_CHANGE：指定用户在首次登录时是否必须修改密码。
例子如下：
由于使用了CHECK_POLICY=ON，所以密码复杂度要足够强。
USE master
GO
CREATE LOGIN Danni
WITH PASSWORD='C0mplexPa$$w0rd',
DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=ON ;
GO 实例间登录名迁移：
即把一个实例的登录名迁移到另外一个实例，这个在过去意义不大，但是随着多种高可用技术的产生及新版本的发布，把现有实例的内容迁移到新的实例变得越来越频繁，但是如果登录名很多，迁移过程就很痛苦，并且会出现很多遗漏。
常见的需要迁移的场景有DR（灾难恢复）方案中，需要预先创建账号，在故障切换时，可以减少停机时间。对于Windows身份验证，只要都在同一个域，问题不大，虽然SID存储在master库，但是安全主体本身，是由Windows自己管理的。你可以预先后者在需要时，在新实例上直接创建，不需要任何修改。
但是使用SQL Server身份验证，即使相同的账号密码，SID都是不一样的，而SQL Server在权限检查时依赖的是SID，所以即使账号密码相同，对SQL Server来说还是不同的登录名。此时会出现“孤儿账号/孤立账号”的现象。对此，可以使用ALTER USER 命令并使用WITH LOGIN选项（替换以前的sp_change_users_logins存储过程）来修复。
USE AdventureWorks2016CTP3
GO
ALTER USER Danni WITH LOGIN = Danni ;
但是毕竟是补救，更好的办法是预先创建。在创建时使用WITH SID选项，下面一个SQLCMD脚本可以用来迁移Danni账号，注意需要使用SQLCMD模式：把ProdInstance1中的Danni账号迁移到DRInstance1中。
:CONNECT CARTERSECURESAFE\ProdInstance1
DECLARE @SQL NVARCHAR(MAX) ;
SET @SQL = (SELECT 'CREATE LOGIN '
+ name
+ ' WITH PASSWORD = ''C0mplexPassw0rd'', SID = 0x'
+ CONVERT(NVARCHAR(64), SID, 2)
FROM sys.sql_logins
WHERE Name = 'Danni') ;
:CONNECT CARTERSECURESAFE\DRInstance1
EXEC(@SQL) ; DECLARE @password NVARCHAR(MAX) = 'C0mplexPa$$w0rd' ;
DECLARE @salt VARBINARY(4) = CRYPT_GEN_RANDOM(4) ;
DECLARE @hash VARBINARY(1000) ;
DECLARE @SQL NVARCHAR(MAX) ;
SET @hash = (SELECT 0x0200 + @salt + HASHBYTES('SHA2_512', CAST(@password
AS VARBINARY(MAX)) + @salt)) ;
SET @SQL = (SELECT 'CREATE LOGIN '
+ Name
+ ' WITH PASSWORD = '
+ CONVERT(NVARCHAR(1000), @hash, 1)
+ ' HASHED, SID = 0x'
+ CONVERT(NVARCHAR(64), SID, 2)
FROM sys.sql_logins
WHERE is_disabled = 0
FOR XML PATH('')) ;
SELECT @SQL ; 
除此之外，还可以使用SSIS来迁移，具体见 迁移 SQL Server 登录名使用数据迁移助手 及 How to transfer logins and passwords between instances of SQL Server 和 Transfer SQL Server Jobs Task and Transfer SQL Server Logins Task in SSIS
服务器角色：
SQL Server提供了一系列内建服务器角色。这些角色满足常用需求，可以直接对登录名授予对应角色，则自动继承实例层面的对应权限。这些内建角色也称为固定服务器角色（fixed server roles）。不可修改和删除，只能对其添加或移除登录名。
| 角色 | 描述 |
| sysadmin | 实例层面最高权限。 |
| bulkadmin | 允许成员执行BULK INSERT命令，常用于执行ETL过程的服务账号。 |
| dbcreator | 可以在实例中创建数据库，一旦创建辛苦，登录名自动成为数据库的拥有者（Onwer），并且可以对这个库进行任何操作。但不代表可以对其他现有非该登录创建的库有权限。 |
| diskadmin | 允许成员可以在SQL Server中管理备份设备。 |
| processadmin | 通过T-SQL或者SSMS来停止实例。也可以kill掉运行中的进程。 |
| public | 所有登录都默认添加到public角色。不可去除。 |
| securityadmin | 成员可以管理实例级别的登录名，比如可以把登录名添加到除sysadmin外的服务器角色，或者对实例级别资源如端点授权。但是不能对数据库层面的用户进行授权。 |
| serveradmin | 包含了diskadmin和processadmin角色，外加启动和停止实例，但是成员如果使用SHUTDOWN T-SQL命令，也能关闭服务（注意关闭跟停止，如果使用NOWAIT选项，则关闭时不需要运行CHECKPOINT），同时成员还可以修改端点和查看所有实例元数据。 |
| setupadmin | 其成员可以创建和管理linked server（链接服务器） |
从SQL Server 2012开始，还可以进行自定义服务器角色，用于补充固定服务器角色的权限。比如可以创建一个叫AvailabilityRole的服务器角色用于部署AlwaysON 可用性组，并且授权这个组具有：
- 修改任意Availability Group
- 修改任意端点
- 创建Availability Group
- 创建端点
这些对于AlwaysOn来说必须的组件。
也可以创建一个专用的初级DBA服务器角色，去除部分重要且不容易掌握的功能的控制权限。下面是演示例子，注意这里使用了GRANT命令，T-SQL中，有三类DCL（控制定义语言）：GRANT、DENY、REVOKE。
- GRANT：允许安全性主体访问安全对象。可以通过WITH选项使其授权的主体具有授权给其他主体的权限。
- DENY：与GRANT相对，拒绝访问指定安全对象。并且总会覆盖GRANT规则。即优先级比GRANT高。
- REVOKE：GRANT可以理解新增权限，那么REVOKE则可以理解为移除权限。但是如果登录名已经通过服务器角色授权，则直接取消登录本身的权限将不会起效。需要对登录名或服务器角色使用DENY，或者把登录名移除出服务器角色。
CREATE SERVER ROLE AVAILABILITYROLE AUTHORIZATION [CarterSecureSafe\
SQLAdmin] ;
GO
GRANT ALTER ANY AVAILABILITYROLE GROUP TO AVAILABILITYROLE ;
GRANT ALTER ANY ENDPOINT TO AVAILABILITYROLE ;
GRANT CREATE AVAILABILITYROLE GROUP TO AVAILABILITYROLE ;
GRANT CREATE ENDPOINT TO AVAILABILITYROLE ;
GO ALTER SERVER ROLE AvailabilityRole ADD MEMBER Danni ;
GO 凭证：
凭据用于提供访问 SQL Server 实例外部的资源的能力。SQL登录名可以使用凭证访问操作系统层面的资源，SQL Server Agent 代理账号可以使用品种去访问SQL Server Agent子系统如PowerShell或者CmdExec。另外在云计算盛行的今天，凭证可以用于把备份放到Azure中，实现更安全更廉价甚至更具扩展性的架构。
当用于访问操作系统层面资源时，凭证通常记录Windows安全性主体的ID和密码。如果用于备份到Azure，那么凭证会记录Azure存储账号（Azure storage account）的名字和私有密钥。比如下面演示：
CREATE CREDENTIAL URLBackupCredential
WITH IDENTITY = 'CarterSecureSafeStorageAcc'
,SECRET ='\Ydfg\SGdTgJNpVFl992sBv7Bp1gyL61I33wNrTMHGBDdtVcx97F5f6SC5uDi59FeY2/IjxyqsuLU2xrkrNAGT==' ;
下一文介绍：SQL Server 安全篇——SQL Server 安全模型（3）——数据库级别安全性
790
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
