沈云，资深工程师，微软解决方案专家
SQL Server如何指定用户单独权限
上周， 有位朋友给我提出了需求：
为公司应用提供用户分配数据访问权限。顺便总结了下有如下要求：
给某个用户查询所有数据库的权限
给某个用户只有备份数据库的权限
给一个用户只有指定数据库的权限
给一个用户只有某个表的权限
要进行以上任务，首先我们先了解下数据库的权限相关的内容
主体
“主体” 是可以请求 SQL Server 资源的实体。 与 SQL Server 授权模型的其他组件一样，主体也可以按层次结构排列。 主体的影响范围取决于主体定义的范围（Windows、服务器或数据库）以及主体是否不可分或是一个集合。 例如，Windows 登录名就是一个不可分主体，而 Windows 组则是一个集合主体。 每个主体都具有一个安全标识符 (SID)。
Windows级别的主体
Windows域登录名
Windows本地登录名
SQL Server-级的主体
SQL Server登录名
服务器角色
数据库级的主体
数据库用户
数据库角色
应用程序角色
SQL Server sa登录名
SQL Server sa登录名是服务器级的主体。 默认情况下，该登录名是在安装实例时创建的。
public数据库角色
每个数据库用户都属于 public数据库角色。 当尚未对某个用户授予或拒绝对安全对象的特定权限时，则该用户将继承授予该安全对象的public角色的权限。
INFORMATION_SCHEMA和sys
每个数据库都包含两个实体：INFORMATION_SCHEMA和sys，它们都作为用户出现在目录视图中。 这两个实体是SQL Server所必需的。 它们不是主体，不能修改或删除它们。
基于证书的SQL Server登录名
名称由双井号 (##)括起来的服务器主体仅供内部系统使用。 下列主体是在安装SQL Server时从证书创建的，不应删除。
##MS_SQLResourceSigningCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLAuthenticatorCertificate##
##MS_AgentSigningCertificate##
##MS_PolicyEventProcessingLogin##
##MS_PolicySigningCertificate##
##MS_PolicyTsqlExecutionLogin##
guest用户
每个数据库包括一个guest。 授予guest用户的权限由对数据库具有访问权限，但在数据库中没有用户帐户的用户继承。 不能删除guest用户，但可通过撤消该用户的CONNECT权限将其禁用。 可以通过在master或tempdb以外的任何数据库中执行REVOKE CONNECT FROM GUEST来撤消CONNECT权限。
客户端和数据库服务器
根据定义，客户端和数据库服务器是安全主体，可以得到保护。 在建立安全的网络连接前，这些实体之间可以互相进行身份验证。 SQL Server支持Kerberos身份验证协议，该协议定义客户端与网络身份验证服务交互的方式。
创建数据库用户
SQL 2016 中支持11种用户类型：
用户基于登录名在master这是最常见的用户类型。
基于登录名基于的 Windows Active Directory 帐户的用户。 CREATE USER [Contoso\Fritz];
基于 Windows 组的登录名的用户。 CREATE USER [Contoso\Sales];
基于使用 SQL Server 身份验证的登录名的用户。 CREATE USER Mary;
在数据库进行身份验证的用户建议以帮助使你的数据库可移植性。
始终允许在 SQL Database。 中包含的数据库中只允许存在 SQL Server。
基于无登录名的 Windows 用户的用户。 CREATE USER [Contoso\Fritz];
基于无登录名的 Windows 组的用户。 CREATE USER [Contoso\Sales];
中的用户 SQL Database 或 SQL 数据仓库 基于 Azure Active Directory 的用户。 CREATE USER [Contoso\Fritz] FROM EXTERNAL PROVIDER;
拥有密码的包含数据库用户。 (在中不可用 SQL 数据仓库。) CREATE USER Mary WITH PASSWORD = '********';
基于Windows主体通过Windows组登录名进行连接的用户
基于无登录名但可通过 Windows 组中的成员身份连接到数据库引擎的 Windows 用户的用户。 CREATE USER [Contoso\Fritz];
基于无登录名但可通过其他 Windows 组中的成员身份连接到数据库引擎的 Windows 组的用户。 CREATE USER [Contoso\Fritz];
无法进行身份验证的用户这些用户无法登录到 SQL Server 或 SQL Database。
没有登录名的用户。 不能登录，但可以被授予权限。 CREATE USER CustomApp WITHOUT LOGIN;
基于证书的用户。 不能登录，但可以被授予权限，也可以对模块进行签名。 CREATE USER TestProcess FOR CERTIFICATE CarnationProduction50;
基于非对称密钥的用户。 不能登录，但可以被授予权限，也可以对模块进行签名。 CREATE User TestProcess FROM ASYMMETRIC KEY PacificSales09;
下面的图片显示了创建数据库用户需要的选项的含义：
创建用户可以使用界面完成：
也可以使用T-SQL 来进行创建
-- 创建登录名：Test 密码是： '123456'.
CREATE LOGIN Test
WITH PASSWORD = '123456';
GO
上面说完了用户，下面说下数据库的角色和权限
服务器级别的权限
SQL Server提供服务器级角色以帮助你管理服务器上的权限。 这些角色是可组合其他主体的安全主体。 服务器级角色的权限作用域为服务器范围。 （“角色”类似于Windows操作系统中的“组”。）
SQL Server提供了九种固定服务器角色。 无法更改授予固定服务器角色的权限。 从SQL Server 2012开始，您可以创建用户定义的服务器角色，并将服务器级权限添加到用户定义的服务器角色。
你可以将服务器级主体（SQL Server登录名、Windows帐户和Windows组）添加到服务器级角色。 固定服务器角色的每个成员都可以将其他登录名添加到该同一角色。 用户定义的服务器角色的成员则无法将其他服务器主体添加到角色。
下表显示了服务器级的固定角色及其权限
下表显示了固定数据库角色及其能够执行的操作。 所有数据库中都有这些角色。 无法更改分配给固定数据库角色的权限
无法更改分配给固定数据库角色的权限。 下图显示了分配给固定数据库角色的权限：
SQL 2016有一些数据库的特殊权限
msdb角色
msdb数据库中包含下表显示的特殊用途的角色。
使用R Services
SQL Server（从SQL Server vNext开始）
安装R Services时，其他数据库角色可用于管理包
下面讲如何实现文章前面说的需求：
给某个用户查询所有数据库的权限
给某个用户只有备份数据库的权限
给一个用户只有指定数据库的权限
给一个用户只有某个表的权限
给某个用户查询所有数据库的权限
创建一个用户
USE [master]
GO
CREATE LOGIN [Test1] WITH PASSWORD=N'password@123'
使用Test1连接数据库实例
可以看到数据库列表， 但是无法访问数据库，
赋予test1对FinaceDemo的读取权限
USE [FinaceDemo]
GO
CREATE USER [Test1] FOR LOGIN [Test1]
ALTER ROLE [db_datareader] ADD MEMBER [Test1]
GO
这样就可以给test1用户对finacedemo的读取权限
给某个用户只有备份数据库的权限
Test1对于finacedemo无备份权限
赋予备份权限
ALTER ROLE [db_backupoperator] ADD MEMBER [Test1]
给一个用户只有指定数据库的权限
我们需要Test1只能看到FinanceDemo，其他所有数据库都不能看到
执行下面脚本
USE [master]
Deny VIEW any DATABASE TO Test1;
go
运行后的效果
执行：
ALTER AUTHORIZATION ON DATABASE::FinanceDemo TO test1
完成后结果：
给一个用户只有某个表的权限
创建测试用户test3
USE [master]
GO
CREATE LOGIN [Test3] WITH PASSWORD=N'password@123'
-----赋予test2可以登录testDB
USE [testdb]
GO
CREATE USER [Test3] FOR LOGIN [Test3]
GO
赋予test3对于t2表的update和select权限
grant update on dbo.t2 to test3
grant select on dbo.t2 to test3
use testDB
查看test3用户获得的权限
exec sp_helprotect @username = 'test3'
执行select * from t2
执行插入操作失败。
以上实现了对数据库权限很细致的管理，更加详细的控制可以参考technet上面的信息。
权限管理非常复杂，以上只是做了简单的介绍。需要更加详细的内容，需要自己去研究。在technet上可以找到更加详细的信息。
立即访问http://market.azure.cn
网友评论