如何授予客户的所有权利和特权 db_owner 将数据库角色固定为应用程序角色?

短版

GRANT CONTROL ON [DatabaseName] TO [ApplicationRoleName];

这是我想要的,但它失败了:

Msg 15151,16级,状态1,第23行

研究工作

我正在使用SQL Server进行调查 应用程序角色

• 它就像一个用户(因为它有用户名和密码)
• 这就像一个角色

自身作为应用程序:

EXECUTE sp_SetAppRole @rolename = 'Contoso.exe', @password =
'Tm8gaSBkaWRuJ3QganVzdCBiYXNlNjQgZW5jb2RlIGEgcGFzc3dvcmQuIEl0J3Mgb25seSBhbiBleGFtcGxlIQ==';

db_所有者的权限

通常情况下,应用程序以用户身份登录 使用者 谁是该组织的成员 db_owner 角色具有以下权限:

• 在每张桌子上
• 每个存储过程、函数
• 对于所有现有对象
• 以及未来存在的所有物体

同时:

• 你可以放置一个 变成 数据库角色
• 你可以放置一个 使用者 变成

您不能放置 应用程序角色 变成

角色的权限

this page's suggestions :

GRANT SELECT, INSERT, UPDATE, DELETE ON Users TO [Contoso.exe];

这很有趣,但它并没有授予所有特权——它只授予 SELECT , INSERT UPDATE 和 DELETE . 我想同意 每件事 -尤其是当我不知道所有的特权是什么(或者可能是什么)的时候。

我盲目地尝试:

GRANT ALL ON Users to [Contoso.exe];

“消息”选项卡中出现以下内容:

好的,那么 ALL 不是全部。太可怕了。

所以我回到:

但这并不意味着一切。例如,我碰巧知道有一种能力可以继续授予他人特权(一种 db_所有者

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;';

尽管如此,我还是错过了一些特权:

• 选择
• 使现代化
• 删去

当然,我可以更新我的脚本:

EXECUTE sp_msForEachTable 'GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER ON ? TO [Contoso.exe] WITH GRANT OPTION;';

但是,与其说是猫捉老鼠的猜谜游戏,不如说是:我想同意 全部的

万事大吉

全部的 不是全部。但他们确实如此 document what all does grant:

| Permission | Table | View | SP | Scalar UDF | Table UDF |
|------------|-------|------|----|------------|-----------|
| SELECT     |  ✔️   |  ✔️ |    |            |     ✔️    |
| INSERT     |  ✔️   |  ✔️ |    |            |     ✔️    |
| UPDATE     |  ✔️   |  ✔️ |    |            |     ✔️    |
| DELETE     |  ✔️   |  ✔️ |    |            |     ✔️    |
| REFERENCES |  ✔️   |  ✔️ |    |    ✔️      |     ✔️    |
| EXECUTE    |       |      | ✔️ |    ✔️     |            |
| ALTER      |  ❌   |  ❌ | ❌ |    ❌     |      ❌   | 

结果证明他们都被欺骗了。已为其他用户创建权限。一 permission to rule them all :

控制 :

• 改变
• 删去
• 处决
• 插入
• 接收
• 控制
• 当家作主
• 视图更改跟踪
• 视图定义

那好多了。与其知道所有的权限,我只授予一个。我不知道哪些权限适用于什么类型的对象,而是只授予一个权限。因为这条线:

我不必 GRANT WITH GRANT

-- when you have CONTROL you also get WITH GRANT for free
EXECUTE sp_msForEachTable 'GRANT CONTROL ON ? TO [Contoso.exe];';

适用于所有对象

我的问题是,我需要为数据库中的每个对象授予控制权限。无论何时添加任何新对象,我都必须确保返回并将其添加到应用程序角色。

重申一下,如果你同意的话 CONTROL 在 ,则您将拥有所有权限:

• 关于数据库
  • 所有对象
  • 数据库中的所有程序集

那个 这就是我想要的。我想 GRANT CONTROL 许可 Grobber 数据库到 [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.:

编辑:警告: 不要

长的

当您的应用程序(即web服务器)尝试打开新连接时,它会从连接池中获取一个连接。第一件事 是否尝试将连接状态重置回默认状态(使用 sp_reset_connection ).

sp_重置_连接 试图做的就是撤销你是 使用者这是不允许的(因为服务器不知道你以前是谁)。所以通过使用 应用程序角色 ,当您尝试连接到服务器时,会突然出现错误。

唯一的办法 “修复” 这是为了禁用连接池。

这是你想要的东西 想做什么。

因此,解决办法是 不 在生产设置中使用应用程序角色。