I am trying to understand the securables of SQL Server, I am not sure on what is the exact use of OwnedSchema
. Can someone help me on this, where it plays a significant role?
Introduction to SchemaA user owns that owns the schema is known as schema owner. It is a useful mechanism to segregate database objects for different applications, access rights, managing the security administration of databases. We do not have any restrictions on the number of objects in a schema.
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.
Benefits of database schemasAccess and security: Database schema design helps organize data into separate entities, making it easier to share a single schema within another database.
The schema has an owner, but the owner is not tied to the name. So, if an account owns a schema and the account must be removed from the database, the owner of the schema can be changed without breaking any code. If you do not wish to organize your database objects into schemas, the dbo schema is available.
The dialog on the picture enlist you all database's schemas and marks some schemas as "checked" if current database principal is the owner of these schemas.
So it shows you the result of this query (that you can catch using 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
Using this dialog you can change the owner of the schema to current user, i.e. execute the following code:
ALTER AUTHORIZATION ON SCHEMA::[this_schema] TO [this_user]
This link is also useful to understand 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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With