ElRojo's questions

删除空模式并更改默认模式如何从数据库用户中删除 db_owner 角色？

我决定清理多年来创建的额外架构。有些架构是默认创建的，并且与 SQL 数据库登录名同名。我使用以下查询搜索了不包含任何对象的架构：

SELECT 
    db_name() AS DatabaseName
    ,ds.SCHEMA_NAME AS SchemaName
    ,du.name AS UserName
    ,'USE  ' + db_name() + '; ALTER USER [' + ds.SCHEMA_NAME + '] WITH DEFAULT_Schema=dbo; drop schema if exists [' + ds.SCHEMA_NAME + ']; ' as Fix
FROM INFORMATION_SCHEMA.SCHEMATA ds
JOIN sys.database_principals du ON du.name = ds.SCHEMA_NAME
    AND du.default_schema_name = ds.SCHEMA_NAME
    AND ds.SCHEMA_OWNER = du.name
WHERE NOT EXISTS (
        SELECT 1
        FROM sys.objects o
        JOIN sys.schemas ss ON ss.schema_id = o.schema_id
        WHERE ss.name = ds.SCHEMA_NAME
        )
    AND ds.SCHEMA_NAME NOT IN (
        'dbo'
        ,'guest'
        ,'sys'
        ,'INFORMATION_SCHEMA'
        )

然后使用这个查询（作为示例）来清理它们：

USE  SecondaryDatabase; 
ALTER USER [User2] WITH DEFAULT_Schema=dbo; 
drop schema if exists [User2]; 

我首先将默认架构更改为默认的“ dbo ”。然后，我删除了与用户同名的架构。

删除架构或更改默认架构会从 User2 中删除 db_owner 角色。我们的供应商为修复此问题所做的更改是将 db_owner 角色授予 User2。

我不明白删除一个没有对象的架构并更改默认架构如何会改变此用户的权限。有什么想法吗？

到目前为止我已经做过和研究过的事情：

• https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/ownership-and-user-schema-separation?view=sql-server-ver16
  • 关于用户和模式的基础知识。
• “架构所有者”的用途是什么？
  • 我理解的是，架构所有者仅授予该架构中对象的权限。如果架构中没有对象，则删除架构不应更改用户权限。
• 将数据库的副本还原到另一台服务器。重新运行“修复”查询，查看它是否会将用户从 db_owner 角色中删除。结果没有。
• 在同一台服务器上恢复了数据库的第二个副本。在运行“修复”后，使用 Visual Studio 中的 Schema Compare 将恢复的原始副本与恢复的副本进行比较。我没有观察到任何意外的变化。将恢复的副本与生产数据库进行了比较。
• SQL Server的具体版本是2019 15.0.4410.1。