除了覆蓋或創建數據庫之外，允許其他一切的正確權限是什麼？

我有一個 AD 組mycompany\developers目前所在的測試環境sysadmin。

最近我遇到了一些問題，特別是關於恢復數據庫的人。我的擔憂是：

1. 有時數據庫參與複製
2. 權限被覆蓋 - 正確的方法是在還原之前保存目前權限並在還原後重新應用它們。
3. 您是否檢查了所有驅動程序中的目前可用磁碟空間，特別是在此還原中使用的驅動程序？
4. 可能有其他人在使用或處理該特定數據庫，並且在沒有通信的情況下對其進行恢復可能會導致其他人的工作失去
5. 在開發環境中，數據庫可以處於簡單恢復模式。您是否縮小了日誌並將數據庫更改為簡單恢復模式。
6. 在這種特殊情況下，每個使用者數據庫的完整備份將在每晚自動執行。但無論如何，您是否檢查了計劃的備份？
7. 您是否從伺服器中刪除了用於還原的備份？
8. 文件夾和驅動器或數據和日誌在現場和測試中是不同的。你確定你把文件移到了正確的地方嗎？
9. 你看過孤立的使用者和登錄嗎？

當開發人員創建新數據庫時，我尤其擔心，即使在開發環境中也是如此。為什麼？

因為他們首先在 dev 中創建數據庫，然後要求我將其複製到 live 中。你有沒有為這個數據庫做任何容量規劃？它應該有多大，一個月內會增長多少？

1. 您是否為 demainding 對象\索引隔離或考慮了不同的文件組？
2. 您是否檢查過所有驅動程序中目前可用的磁碟空間，特別是在創建數據庫時使用的那些？
3. 事務日誌有多大，為什麼？最初我喜歡將它設置為最大聚集索引的 1.5 倍。
4. 什麼是自動增長設置？
5. 目前的權限是什麼？

我意識到有些人不是那麼容易對付的，我寧願減少他們對該伺服器的控制。

我不想做他們的工作，但我想通過恢復或附加文件等方式限制他們創建新數據庫或覆蓋現有數據庫。

他們仍然應該能夠創建和執行作業，使用使用者數據庫創建\更改任何對象，創建登錄名，授予權限等……

基本上問題是：

如何限制目前系統管理員的權力？我考慮過不授予系統管理員，而是授予一組伺服器權限（不包括創建/更改數據庫的權限），這將允許他們執行其他所有操作。

那組伺服器權限是什麼？

TL; 博士

為了限制 DEV 做任何“錯誤”的事情，您至少必須刪除sysadminSQL Server 角色。

開始吧

您可以為您的 DEV 分配db_owner每個單獨數據庫的數據庫角色。但是，該角色將允許使用者執行BACKUP DATABASE ...或BACKUP LOG ...您提到您不希望他們能夠執行的操作。

解決方案

從 SQL Server 2014 開始，您可以使用該db_owner角色限制 SQL Server 登錄，從而進一步刪除個人特權/權限。一個範例是通過在數據庫上發出以下命令來刪除數據庫和事務日誌級別的備份權限：

USE <database_name>
GO
DENY BACKUP DATABASE TO <database_principal>
GO
DENY BACKUP LOG TO <database_principal>

參考： 拒絕數據庫權限 (Transact-SQL) (Microsoft Docs)

權限分為三個級別。

GRANT  : You are granted/granting a specific permission  
REVOKE : A previously granted/denied permission is revoked (removed) again  
DENY   : You are denied/denying a specific permission  

選擇

現在因為DENY優先於GRANT您可以使用創建新數據庫角色並將其添加<database_principals>到該角色的替代解決方案：

USE <database_name>
GO
CREATE ROLE [deny_backup]
GO
DENY BACKUP DATABASE TO [deny_backup]
GO
DENY BACKUP LOG TO [deny_backup]
GO
EXEC sp_addrolemember N'deny_backup', N'<database_principal>'
GO

參考： 限制使用者的備份權限（社交 MSDN）

分步指南

因為有些人對我在這篇文章中所做的陳述持懷疑態度，所以我添加了單獨的步驟來向您展示如何確實拒絕SQL 登錄的權限，即使該登錄具有db_owner角色。

1. 創建和映射 SQL 登錄

讓我們創建一個 SQL 登錄並為其分配db_owner數據庫角色：

2. 拒絕備份數據庫權限

現在我們切換到具有SQL Server 角色和從數據庫主體訪問權限的更高特權帳戶，例如saWindows 登錄名（可能是您，問這個問題的 DBA）：sysadmin``DENY``backup database``hot2use

在一個完美的世界裡，你也需要送出一個DENY BACKUP LOG TO hot2use。 3. 使用hot2use登錄伺服器並備份數據庫

看到數據庫hot2use具有db_owner數據庫角色，我們使用…Test登錄hot2use

…並嘗試備份數據庫：

好吧，那是行不通的，這是我們所期望的。這是因為 SQL Server 登錄hot2use名不再具有備份數據庫的權限，即使該登錄名仍然具有數據庫角色db_owner： 4. SQL Login 想要破解權限

鑑於 SQL Server 登錄具有db_owner角色，並且根據某些人的說法，他可以為自己分配權限，讓我們嘗試一下：

（測試是在全新的數據庫 SQL Server 查詢視窗中進行的） 5. 概括

如果您DENY擁有更高特權帳戶的權限，那麼這些特權就不GRANT能從特權較低的帳戶收回，即使 SQL 登錄（例如hot2use）應該從數據庫角色中獲得這些特權db_owner。這在 SQL Server 2014 中有所改變（是的，測試是在 SQL Server 2014 實例上進行的）。

其他資源

您可以根據可以在此處找到的官方概述進一步限制權限：

權限（數據庫引擎）

進一步推薦閱讀：

開始使用數據庫引擎權限

等等，恢復…

哦，是的，我們只是否認了BACKUP DATABASE特權，不是嗎？這與 無關RESTORE DATABASE，或者是嗎？讓我們來了解一下。我將使用更高權限的帳戶/登錄名創建數據庫備份：

似乎工作。讓我們切換回hot2useSQL Server 查詢視窗並恢復備份：

如您所見，它不起作用，因為該BACKUP DATABASE權限還限制了該 SQL Server 登錄的恢復能力。

沒有備份權限 = 沒有恢復權限。

怎麼樣DENY BACKUP DATABASE FROM SYSADMIN？

好吧，既然你問了。讓我們試試看。我將省略顯示我如何撤銷數據庫角色的螢幕截圖。

我為 SQL 登錄分配了sysadminSQL Server 角色：

ALTER SERVER ROLE [sysadmin] ADD MEMBER [hot2use]
GO

…然後使用上述DENY BACKUP DATABASE..命令刪除備份數據庫的權限，然後使用以下命令執行數據庫備份：

backup database Test to disk = 'C:\temp\Test_Full_Backup_20171121.bak'

…導致：

因此，您不能DENY從具有sysadmin伺服器角色的 SQL 登錄中獲得權限，但DENY如果 SQL 登錄沒有“sysadmin”數據庫角色，則您可以獲得權限。

備份/恢復數據庫所需的最低權限

如果我絕對沒有為 SQL Server 登錄分配 SQL Server 角色並且只分配db_backupoperator給定數據庫上的數據庫角色，那麼我可以備份和恢復數據庫。這是其他人認為 SQL 登錄無法執行的事情。較高權限的帳戶創建了備份，因此較低權限的帳戶（非系統管理員）不應該能夠進行備份/恢復。

在將 SQL Server 登錄的所有權限重置hot2use為public伺服器和db_backupoperator數據庫級別後，我會看到以下列舉權限：

根據其他人的說法，我不應該能夠執行備份，因為我缺少 SQL Server 角色。

所以如果我有這個db_backupoperator角色，我實際上可以備份一個數據庫。

再說一次，如果我DENY BACKUP DATABASE登錄到 SQL Server（在 SQL Server 2014 實例或更高版本上），那麼我將不再能夠備份我的數據庫，即使我仍然擁有db_backupoperator數據庫級別的角色：

如果 SQL 登錄hot2use試圖撤銷先前被拒絕的權限，那麼這將失敗，並顯示我之前在上面顯示的類似消息。

概括

確定要分配給 DEV 的權限/特權/角色將是一個令人厭煩的鬥爭/問題，因為他們不想放棄 sysadminSQL Server 角色。您將必須找出他們完成工作所需的SQL Server 角色和**數據庫角色。**最壞的情況是一切都將保持不變。最好的情況是您將能夠限制 DEV 的權限/特權。

您至少必須sysadmin從您的 DEV 中刪除 SQL Server 角色，以進一步限制他們能夠做任何他們想做的事情。但是一旦刪除了該 SQL Server 角色，您就可以為其分配db_owner角色，並DENY BACKUP DATABASE ...禁止他們備份或恢復數據庫。

開發人員仍然可以在數據庫中做任何他們想做的事情（包括 a DROP DATABASE），但他們不能執行備份或恢復。請參閱以下列舉權限，以獲取hot2use具有數據庫角色db_owner但同時具有DENY BACKUP DATABASE ...和的 SQL Server 登錄DENY BACKUP LOG ...：

SQL Server 使用 db_owner 角色登錄但沒有備份權限

引用自：https://dba.stackexchange.com/questions/191391