SQL Server 2016 SP2无法授予CREATE LOGIN权限问题咨询

SQL Server 2016是否支持GRANT CREATE LOGIN？ #

SQL Server 2016及更早版本不支持GRANT CREATE LOGIN这个独立权限，该权限是在SQL Server 2019才正式引入的（包含后续的2022版本），所以你在2016执行该语句会报语法错误。

在SQL Server 2016中实现需求的方案 #

要让用户仅能创建新登录名、修改自己创建的登录名，且无法访问现有登录名，需要通过权限授予+DDL触发器+自定义视图组合实现：

1. 授予基础权限 #

首先授予用户创建登录名所需的最低权限（2016中只能用ALTER ANY LOGIN，后续通过触发器限制其操作范围）：

GRANT ALTER ANY LOGIN TO [loginname];

2. 记录登录名的创建者 #

在master库中创建表，用于存储每个登录名的创建者信息：

USE master;
GO
CREATE TABLE LoginCreators (
    LoginName sysname PRIMARY KEY,
    CreatorLogin sysname NOT NULL,
    CreateDate DATETIME NOT NULL DEFAULT GETDATE()
);
GO
-- 允许目标用户操作该表，用于触发器写入和查询
GRANT INSERT, SELECT ON LoginCreators TO [loginname];

3. 创建服务器级DDL触发器 #

通过触发器拦截非法操作：创建登录名时自动记录创建者；修改登录名时验证当前用户是否为该登录名的创建者，非创建者操作则回滚：

USE master;
GO
CREATE TRIGGER RestrictLoginAlter
ON ALL SERVER
FOR CREATE_LOGIN, ALTER_LOGIN
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData XML = EVENTDATA();
    DECLARE @LoginName sysname = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');
    DECLARE @CurrentUser sysname = SUSER_SNAME();

    -- 记录新登录名的创建者
    IF @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') = 'CREATE_LOGIN'
    BEGIN
        INSERT INTO LoginCreators (LoginName, CreatorLogin)
        VALUES (@LoginName, @CurrentUser);
    END
    -- 验证修改登录名的权限
    ELSE IF @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') = 'ALTER_LOGIN'
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM LoginCreators WHERE LoginName = @LoginName AND CreatorLogin = @CurrentUser)
        BEGIN
            RAISERROR('你只能修改自己创建的登录名。', 16, 1);
            ROLLBACK TRANSACTION;
        END
    END
END
GO

4. 限制对现有登录名的访问 #

拒绝用户查看系统视图sys.server_principals的权限，防止其访问所有登录名：

DENY SELECT ON sys.server_principals TO [loginname];

5. 提供自定义视图让用户查看自己创建的登录名 #

创建视图仅返回用户自己创建的登录名，并授予查询权限：

USE master;
GO
CREATE VIEW MyCreatedLogins
AS
SELECT sp.name, sp.type_desc, sp.create_date
FROM sys.server_principals sp
JOIN LoginCreators lc ON sp.name = lc.LoginName
WHERE lc.CreatorLogin = SUSER_SNAME();
GO
GRANT SELECT ON MyCreatedLogins TO [loginname];

内容的提问来源于stack exchange，提问作者rgorr