Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm currently learning SQL Server 2008 and have come across schemas and there is one thing I don't understand about them that they have to have an owner; why can't they own themselves?

My background is mostly with Java and it sounds like a schema is like a package which does not have an owner.

share|improve this question

2 Answers 2

Every object in SQL Server has to be owned by someone. Ownership is used to control permissions. For example, any object that you own you have direct access to without it having to be granted to you. An other example is a procedure that has access to any object owned by the same owner. This allows someone with only access to the procedure to execute it even if it is accessing objects that are owned by someone else.

A schema is a grouping mechanism that most other RDBMSs dont have. (If an other RDBMS refers to a "schema" it means something more like what we call a "database".) Schemata allow to group objects of different types together into logical units within a single database. By default, the ownership of the schema is passed through directly to all objects in that schema. If you change the owner of the schema, the owner of all objects within changes too. (this can be changed on an object by object basis however.)

On the other end, you can grant access to a schema which in turn grants access to all objects within. This makes managing permissions a lot simpler.

Be aware, that you should always fully qualify every object with schemaname.objectname. The reason for this is two fold:

  1. Performance: SQL Server tries to figure out the schema of an object that was referenced without schema specification based on different settings and rules. This burns precious cpu cycles unnecessarily.
  2. While rare, it can lead to security and other access problems because the wrong object might be selected by SQL Server if multiple objects with the same name exist in different schemata.
share|improve this answer

Schemas are securable resources. They are owned by principals, specifically database users. A schema cannot own itself.

Schemas and database users can, however, have the same name. For example, there might be a schema called Alice owned by a database user called Alice. For example there is a schema called dbo that is owned by database user dbo.

In some versions of SQL Server, when you created a database user then a schema with the same name was created, which lead to confusion.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.