Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is the meaning/use of "Owned Schemas"?

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?

enter image description here

like image 759
N.Dinesh.Reddy Avatar asked Nov 24 '17 09:11

N.Dinesh.Reddy


People also ask

What is an owned schema?

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.

Who owns 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.

Why do we use schemas in database?

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.

Is owner the same as schema?

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.


1 Answers

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.

like image 120
sepupic Avatar answered Oct 18 '22 11:10

sepupic