如何将“db_owner”固定数据库角色的所有权限授予应用程序角色？
简短版本
命令：
GRANT CONTROL ON [DatabaseName] TO [ApplicationRoleName];
我想要的是这样的，但它失败了：
Msg 15151，Level 16，State 1，Line 23
无法找到对象“DatabaseName”，因为它不存在或您没有权限。
研究工作
我正在研究使用SQL Server应用程序角色。
- 它类似于用户（具有用户名和密码）
- 它也像一个角色
一旦连接到服务器，您的应用程序将运行存储过程来"登录"自身作为应用程序：
EXECUTE sp_SetAppRole @rolename = 'Contoso.exe', @password =
'Tm8gaSBkaWRuJ3QganVzdCBiYXNlNjQgZW5jb2RlIGEgcGFzc3dvcmQuIEl0J3Mgb25seSBhbiBleGFtcGxlIQ==';
db_owner 的权限
通常应用程序登录为一个用户，该用户是db_owner 固定角色的成员。 db_owner 角色具有以下权限：
- 对所有内容的完全访问权限
- 对每个表的完全访问权限
- 对每个视图的完全访问权限
- 对所有现有对象的每个存储过程、函数的完全访问权限
- 对所有将来存在的对象的完全访问权限
虽然：
- 您可以将用户放入数据库角色
- 您可以将用户放入应用程序角色
但您不能将应用程序角色放入数据库角色中。
那么问题来了：如何授予我的应用程序角色所有权限（即执行所有操作的权限）？
角色的权限
现在是授予权限给角色的时候了。根据此页面的建议：
GRANT SELECT, INSERT, UPDATE, DELETE ON Users TO [Contoso.exe];
这很有趣，但它并没有授予所有权限 - 它只授予
SELECT、INSERT、UPDATE和DELETE。我想授予全部权限 - 特别是当我不知道所有权限时（或可能是什么）。我盲目尝试：
GRANT ALL ON Users to [Contoso.exe];
以下内容出现在“消息”选项卡中：
ALL 权限已被弃用，仅为兼容性而维护。它并不意味着在实体上定义的所有权限。
好的，所以授予 ALL 并不意味着授予所有权限。这太可怕了。
因此，我回到了：
GRANT SELECT, INSERT, UPDATE, DELETE ON Users TO [Contoso.exe];
除此之外并不能授予所有权限。例如，我碰巧知道有一种能力可以授予他人特权（这是db_owner拥有的一项特权）。因此，我必须修改我的陈述：
GRANT SELECT, INSERT, UPDATE, DELETE ON Users TO [Contoso.exe] WITH GRANT OPTION;
好的，那么这已经更接近了，但它只适用于一个表格。
我需要适用于所有表格的东西：
EXECUTE sp_msForEachTable 'GRANT SELECT, INSERT, UPDATE, DELETE ON ? TO [Contoso.exe] WITH GRANT OPTION;';
尽管如此，我错过了一些权限：
- SELECT ✔️
- INSERT ✔️
- UPDATE ✔️
- DELETE ✔️
- REFERENCES ❌
- ALTER ❌
EXECUTE sp_msForEachTable 'GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER ON ? TO [Contoso.exe] WITH GRANT OPTION;';
但是，与其玩猜谜游戏：我想授予所有权限。
所有几乎就是全部
在上面的警告中，SQL Server指出所有并不能授予全部权限。但是他们确实记录了所有可以授予的权限：
| Permission | Table | View | SP | Scalar UDF | Table UDF |
|------------|-------|------|----|------------|-----------|
| SELECT | ✔️ | ✔️ | | | ✔️ |
| INSERT | ✔️ | ✔️ | | | ✔️ |
| UPDATE | ✔️ | ✔️ | | | ✔️ |
| DELETE | ✔️ | ✔️ | | | ✔️ |
| REFERENCES | ✔️ | ✔️ | | ✔️ | ✔️ |
| EXECUTE | | | ✔️ | ✔️ | |
| ALTER | ❌ | ❌ | ❌ | ❌ | ❌ |
控制所有权限
事实证明，他们全都被欺骗了。因为另一个权限被创建了。一个支配所有权限的权限：
- 控制（CONTROL）
赋予被授权人类似所有权的能力。被授予 CONTROL 的主体在可保护的对象上实际上拥有所有已定义的权限。被授予 CONTROL 的主体也可以在可保护的对象上授予权限。由于 SQL Server 安全模型是分层的，特定范围内的 CONTROL 隐含包括该范围下所有可保护对象的 CONTROL。例如，在数据库上授予 CONTROL 意味着对数据库、数据库中所有程序集、数据库中所有架构以及数据库中所有架构中的对象拥有所有权限。
他们接着列举了当你拥有CONTROL时隐含的权限：
- ALTER（更改）
- CONTROL（控制）
- DELETE（删除）
- EXECUTE（执行）
- INSERT（插入）
- RECEIVE（接收）
- REFERENCES（引用）
- CONTROL（控制）
- TAKE OWNERSHIP（取得所有权）
- UPDATE（更新）
- VIEW CHANGE TRACKING（查看更改跟踪）
- VIEW DEFINITION（查看定义）
这样就好多了。我只需要授予一个权限，而不是知道所有的权限。而且，我只需要授予一个适用于所有对象的权限。而且，由于下面这行：
被授予 CONTROL 权限的主体也可以在可保护的对象上授予权限。
我就不必再使用 GRANT WITH GRANT 了：
-- when you have CONTROL you also get WITH GRANT for free
EXECUTE sp_msForEachTable 'GRANT CONTROL ON ? TO [Contoso.exe];';
对所有对象
我的问题是我需要给数据库中的每个对象授予 CONTROL 权限。并且每当添加新对象时，我必须确保返回并将其添加到应用程序角色。
我需要的是微软所提示的东西：
例如，在数据库上的 CONTROL 意味着对数据库上的所有权限、数据库中的所有程序集的所有权限、数据库中的所有模式的所有权限以及数据库中所有模式中的对象的所有权限。
换句话说，如果你在 数据库 上授予 CONTROL，那么你将拥有所有权限：
- 在数据库上
- 所有对象
- 数据库中的所有程序集
- 所有模式
这就是我想要的。我想要将 Grobber 数据库上的 GRANT CONTROL 权限授予 [Contoso.exe] 应用程序角色：
GRANT CONTROL ON Grobber TO [Contoso.exe];
Msg 15151, Level 16, State 1, Line 23
Cannot find the object 'Grobber ', because it does not exist or you do not have permission.
我可能已经接近解决问题，但只停留在了1码线处。
或者，我可能还远远不够。所以我在S.O.上问：
如何将db_owner权限授予另一个角色？
编辑：警告：不要使用应用程序角色-它会破坏一切
当客户端登录应用程序角色时，该身份是该连接的持久属性。并且在连接池打开（作为ADO.net、ADO和ODBC的首选和默认选项）后，该连接会保持打开状态很长时间，即使您关闭了连接。
当您的应用程序（即Web服务器）尝试打开新连接时，它会从连接池中获取一个连接。
sp_reset_connection是SqlConnection尝试将连接状态重置为默认状态的第一件事。
sp_reset_connection尝试做的一件事是撤消您作为应用程序角色用户的身份。这是不允许的（因为服务器不知道您之前的身份）。因此，如果使用应用程序角色，则在尝试连接到服务器时会突然出现错误。
唯一的“解决办法”是禁用连接池。
这是您不想做的事情。
因此，在生产环境中不要使用应用程序角色。
GRANT CONTROL ON [db] TO [role]无法生效的原因是一个简单的语法问题：应该使用GRANT CONTROL ON DATABASE::[db] TO [role]（DATABASE::范围不可选）。据我所知，这也适用于应用程序角色。我不确定是否更喜欢在数据库上拥有CONTROL权限，还是成为db_owner的成员；我觉得后者更容易看到。 - Jeroen Mostert