tsql - "Owned Schemas"的含义/用途是什么？

Question

我试图了解 SQL Server 的安全性，我不确定 OwnedSchema 的确切用途是什么.有人可以在这方面帮助我吗，它在哪些方面发挥着重要作用？

Answer 1

如果当前数据库主体是这些模式的所有者，则图片上的对话框将列出所有数据库的模式，并将某些模式标记为“已检查”。

所以它向你展示了这个查询的结果(你可以使用 Profiler 捕获):

SELECT
s.name AS [Name],
s.schema_id AS [ID],
ISNULL(dp1.name, N'') AS [Owner],
CAST(
      case when s.name in ('dbo','guest','INFORMATION_SCHEMA','sys','db_owner','db_accessadmin','db_securityadmin','db_ddladmin','db_backupoperator','db_datareader','db_datawriter','db_denydatareader', 'db_denydatawriter') then 1 else 0 end AS bit) AS [IsSystemObject]
FROM
sys.schemas AS s
LEFT OUTER JOIN sys.database_principals AS dp1 
   ON dp1.principal_id = s.principal_id

使用此对话框，您可以将架构的所有者更改为当前用户，即执行以下代码:

ALTER AUTHORIZATION ON SCHEMA::[this_schema] TO [this_user]

这个链接也有助于理解 Ownership and User-Schema Separation in SQL Server .

Schema Owners and Permissions

Schemas can be owned by any database principal, and a single principal can own multiple schemas. You can apply security rules to a schema, which are inherited by all objects in the schema. Once you set up access permissions for a schema, those permissions are automatically applied as new objects are added to the schema. Users can be assigned a default schema, and multiple database users can share the same schema. By default, when developers create objects in a schema, the objects are owned by the security principal that owns the schema, not the developer. Object ownership can be transferred with ALTER AUTHORIZATION Transact-SQL statement. A schema can also contain objects that are owned by different users and have more granular permissions than those assigned to the schema, although this is not recommended because it adds complexity to managing permissions. Objects can be moved between schemas, and schema ownership can be transferred between principals. Database users can be dropped without affecting schemas.