伺服器角色只允許創建伺服器登錄，不能改變一個

我正在使用 Microsoft SQL Server Management Studio，我想創建一個伺服器角色，該角色有權創建伺服器登錄，但只能創建，不能更改！

有誰知道權限名稱是什麼？

CREATE 和 ALTER 綁定在一起成為 ALTER ANY。實際目標是什麼？如果您要授予某人創建登錄名的權限，為什麼要阻止更改？無論您認為他們可能會嘗試做什麼，他們都可以創建一個額外的登錄而不是更改現有的登錄，或者刪除/創建原始登錄。

您可以根據呼叫者通過 DDL 觸發器阻止 ALTER LOGIN 命令，但這與權限完全不同。

雖然我不確定這種設置的實用性，但仍然可以這樣做（即CREATE LOGINonly、noDROP LOGIN和 no ALTER LOGIN）。你甚至可以讓它成為一個角色（根據要求），雖然不是一個內置的伺服器角色。

正如@Aaron 提到的，沒有特定的權限CREATE LOGIN; 僅ALTER ANY LOGIN涵蓋創建、更改和刪除。因此，訣竅是使用證書和模組簽名來（有效地）選擇您想要的一個權限。

在這種方法中，我們將創建一個證書，然後基於該證書創建一個登錄，然後我們將授予該登錄ALTER ANY LOGIN權限。但是這個基於證書的登錄也可以更改和刪除登錄這一事實是無關緊要的，因為我們還將創建一個使用動態 SQL 執行的CREATE LOGIN儲存過程，然後使用用於創建登錄的相同證書對該儲存過程進行簽名。登錄和模組簽名相同的證書將連結登錄和儲存過程，使該儲存過程中的程式碼具有創建、更改和刪除登錄的潛在能力，但如果儲存過程中的程式碼只是為了執行而編寫的CREATE LOGIN，那麼這就是所有可以做的。

最後一步是創建一個數據庫角色，EXECUTE在儲存過程中授予該角色，最後將您想要的任何使用者添加到該數據庫角色。

設置

步驟 1：創建儲存過程和數據庫角色

USE [tempdb];

IF (OBJECT_ID(N'dbo.CreateLogin') IS NULL)
BEGIN
   EXEC(N'CREATE PROCEDURE dbo.CreateLogin AS SET NOCOUNT ON;');
END;
GO
ALTER PROCEDURE dbo.CreateLogin
(
   @Login sysname,
   @Password NVARCHAR(500),
   @DebugMode BIT = 0
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

SET @SQL =  N'CREATE LOGIN '
           + QUOTENAME(@Login)
           + N' WITH PASSWORD = N'''
           + @Password + N''';';

IF (@DebugMode = 0)
BEGIN
 EXEC (@SQL);
END;
ELSE
BEGIN
 PRINT @SQL;
END;
GO
---

CREATE ROLE [LoginCreator];

GRANT EXECUTE ON [dbo].[CreateLogin] TO [LoginCreator];

第 2 步：創建證書並登錄，授予權限，將證書複製到目標數據庫，並簽署儲存過程

EXEC (N'
USE [master];

IF (CERT_ID(N''Permission:AlterAnyLogin$Cert'') IS NULL)
BEGIN
 PRINT ''Creating Certificate in [master]...'';
 CREATE CERTIFICATE [Permission:AlterAnyLogin$Cert]
   ENCRYPTION BY PASSWORD = N''MyCertificate!MineMineMine!''
   WITH SUBJECT = N''Grant the ALTER ANY LOGIN permission'',
   EXPIRY_DATE = ''2099-12-31'';

 PRINT ''Creating Certificate-based Login...'';
 CREATE LOGIN [Permission:AlterAnyLogin$User]
   FROM CERTIFICATE [Permission:AlterAnyLogin$Cert];
END;

GRANT ALTER ANY LOGIN TO [Permission:AlterAnyLogin$User];
');


DECLARE @CertificateBytes VARBINARY(MAX),
       @PrivateKeyBytes VARBINARY(MAX),
       @SQL NVARCHAR(MAX);

EXEC dbo.sp_executesql
 N'
USE [master];

DECLARE @CertID INT;
SET @CertID = CERT_ID(N''Permission:AlterAnyLogin$Cert'');
SELECT @tmpCertificateBytes = CERTENCODED(@CertID),
      @tmpPrivateKeyBytes = CERTPRIVATEKEY(@CertID, N''MyCertificate!MineMineMine!'',
                                           N''MyCertificate!MineMineMine!'');
',
N'@tmpCertificateBytes VARBINARY(MAX) OUTPUT, @tmpPrivateKeyBytes VARBINARY(MAX) OUTPUT',
 @tmpCertificateBytes = @CertificateBytes OUTPUT,
 @tmpPrivateKeyBytes = @PrivateKeyBytes OUTPUT;

USE [tempdb];
SET @SQL = N'CREATE CERTIFICATE [Permission:AlterAnyLogin$Cert] FROM BINARY = '
           + CONVERT(NVARCHAR(MAX), @CertificateBytes, 1)
           + N' WITH PRIVATE KEY ( BINARY = '
           + CONVERT(NVARCHAR(MAX), @PrivateKeyBytes, 1)
           + N', DECRYPTION BY PASSWORD = N''MyCertificate!MineMineMine!'''
           + N', ENCRYPTION BY PASSWORD = N''MyCertificate!MineMineMine!'' );';
PRINT @SQL;
EXEC(@SQL);

ADD SIGNATURE
 TO [dbo].[CreateLogin]
 BY CERTIFICATE [Permission:AlterAnyLogin$Cert]
 WITH PASSWORD = N'MyCertificate!MineMineMine!';

第 3 步：創建測試登錄名和使用者，並將使用者添加到數據庫角色

IF (SUSER_ID(N'JohnnyLunchbucket') IS NULL)
BEGIN
 PRINT 'Creating Login: [JohnnyLunchbucket] ...';
 CREATE LOGIN [JohnnyLunchbucket] WITH PASSWORD = 'WeakPasswurd ;-)';
END;

CREATE USER [JohnnyLunchbucket] FOR LOGIN [JohnnyLunchbucket];

ALTER ROLE [LoginCreator] ADD MEMBER [JohnnyLunchbucket];

測試

測試 1：作為特權登錄/使用者，驗證登錄總數以及測試登錄不存在

SELECT COUNT(*) FROM sys.server_principals; -- 54 (or whatever)
SELECT * FROM sys.server_principals WHERE [name] = N'TempLogin'; -- no rows

測試2：作為低權限測試Login / User，驗證Login總數並嘗試CREATE LOGIN

EXECUTE AS LOGIN = N'JohnnyLunchbucket';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];

SELECT COUNT(*) FROM sys.server_principals; -- 11 (much lower than before)

CREATE LOGIN [TempLogin] WITH PASSWORD = N'Sup,Yo?!';
/*
Msg 15247, Level 16, State 1, Line xxxxx
User does not have permission to perform this action.
*/

測試3：作為低權限測試登錄/使用者，執行儲存過程

EXECUTE dbo.CreateLogin N'TempLogin', N'Sup,Yo?!'; -- SUCCESS!!!!!

測試4：作為低權限測試Login / User，檢查Login總數並嘗試ALTER LOGIN

SELECT COUNT(*) FROM sys.server_principals; -- still 11

ALTER LOGIN [TempLogin] ENABLE;
/*
Msg 15151, Level 16, State 1, Line xxxxx
Cannot alter the login 'TempLogin', because it does not exist or you do not have permission.
*/

測試5：作為特權測試登錄/使用者，檢查登錄總數並嘗試ALTER LOGIN

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];

ALTER LOGIN [TempLogin] ENABLE; -- Success (so it does exist)

SELECT COUNT(*) FROM sys.server_principals; -- 55 (1 more than before)
SELECT * FROM sys.server_principals WHERE [name] = N'TempLogin'; -- Now it's there!!

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