如何让MS SQL Server 2012用户组仅在自身用户名架构下创建对象？

当然可以实现这个需求！在SQL Server 2012中，我们可以通过架构权限控制和默认架构绑定的组合来精准限制用户的对象创建范围，下面是具体的操作步骤：

1. 确保用户与对应架构存在并绑定所有权 #

首先，对于每个目标用户（比如foo\bar），我们需要创建以其用户名为名的架构，并将该架构的所有者设置为该用户——这能让用户对自己的架构拥有核心控制权：

-- 先确保用户已存在于数据库中（如果是Windows域用户，需要先创建登录再映射用户）
CREATE USER [foo\bar] FOR LOGIN [foo\bar];

-- 创建对应用户名的架构，并设置所有者为该用户
CREATE SCHEMA [foo\bar] AUTHORIZATION [foo\bar];

2. 设置用户的默认架构为自身对应的架构 #

将用户的默认架构设置为同名架构后，用户创建对象时如果不指定架构，默认会自动落到自己的架构下，避免误操作：

ALTER USER [foo\bar] WITH DEFAULT_SCHEMA = [foo\bar];

3. 精准配置权限：仅允许用户在自身架构创建对象 #

接下来要确保用户只有在自己的架构下有创建对象的权限，同时移除可能存在的全局或其他架构的权限：

• 首先授予用户在自身架构下的DDL权限（以常见的表、视图、存储过程、函数为例）：

-- 授予用户在自身架构下创建对象的权限
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION TO [foo\bar];
-- 授予用户对自身架构的修改、控制权（允许在架构内管理对象）
GRANT ALTER, CONTROL ON SCHEMA::[foo\bar] TO [foo\bar];

• 然后移除用户可能拥有的跨架构权限：
  • 如果用户属于db_ddladmin这类拥有全局DDL权限的数据库角色，需要将其移出：

EXEC sp_droprolemember 'db_ddladmin', 'foo\bar';

• 如果之前给用户授予过其他架构的创建权限，要撤销：

REVOKE CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION ON SCHEMA::[dbo] FROM [foo\bar];
-- 替换[dbo]为其他需要限制的架构名

4. 验证权限是否生效 #

可以通过模拟用户操作来验证权限是否正确配置：

-- 切换到目标用户身份
EXECUTE AS USER = 'foo\bar';

-- 尝试在其他架构创建对象（应该报错）
CREATE TABLE dbo.UnauthorizedTest (ID INT);

-- 尝试在自身架构创建对象（应该成功）
CREATE TABLE [foo\bar].AuthorizedTest (ID INT);

-- 切回原身份
REVERT;

批量处理建议 #

如果需要为多个用户配置相同规则，可以用动态SQL批量执行，比如遍历数据库中的非系统用户：

DECLARE @UserName NVARCHAR(128);
DECLARE UserCursor CURSOR FOR
    SELECT name FROM sys.database_principals
    WHERE type IN ('U', 'S') AND name NOT LIKE '##%' AND name NOT IN ('dbo', 'guest', 'sys');

OPEN UserCursor;
FETCH NEXT FROM UserCursor INTO @UserName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 创建用户（如果不存在）
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @UserName)
    BEGIN
        EXEC('CREATE USER [' + @UserName + '] FOR LOGIN [' + @UserName + ']');
    END

    -- 创建架构（如果不存在）
    IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = @UserName)
    BEGIN
        EXEC('CREATE SCHEMA [' + @UserName + '] AUTHORIZATION [' + @UserName + ']');
    END

    -- 设置默认架构
    EXEC('ALTER USER [' + @UserName + '] WITH DEFAULT_SCHEMA = [' + @UserName + ']');

    -- 授予权限
    EXEC('GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION TO [' + @UserName + ']');
    EXEC('GRANT ALTER, CONTROL ON SCHEMA::[' + @UserName + '] TO [' + @UserName + ']');

    FETCH NEXT FROM UserCursor INTO @UserName;
END

CLOSE UserCursor;
DEALLOCATE UserCursor;

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