CODEWITHSUNDEEP
t-sqlsql-server-2012database-administration
N.Dinesh.Reddy
N.Dinesh.Reddy

Reputation: 632

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

Upvotes: 8

Views: 11846

Answers (1)

sepupic
sepupic

Reputation: 8687

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.

Upvotes: 1

Related Questions