SQL Server roles, schemas, users

44
votes

I've been trying to figure out why SQL Server has db_owner schema and db_owner role? This is very confusing. I've been searching for answers and so far this is how my understanding goes:

  1. All tables and objects (such as constraints etc) belong to a schema. DBO being the default schema.
  2. A user may be given permission to edit each object or the schema. A permission on the schema extends the permission to all objects within that schema. So you don't have to grant permission on each individual object.
  3. A role groups permissions together for convenience.

If any of this is incorrect let me know. But I think so far so good. Now my questions are:

  1. What exactly is db_owner schema as seen in "Database User" dialog box of SQL Server Management Studio? And on the same dialog, you define the "Default Schema" as dbo. Why aren't the two the same? If by default SQL Server uses dbo to create all objects under, what use is db_owner?
  2. Why would a user want to own a schema? You are assigning permissions/roles already. What does owning db_accessadmin give you?
  3. Can you give an example of when you create objects under db_owner schema and db_accessadmin schema? In other words does anyone legitimately use those schemas?
2
sql-serversql-server-2005
Agreed. It was ridiculous of them to create two different kinds of objects (a schema and role) with the same name. – LarryBud

2 Answers

28
votes

A SQL Server schema is simply container of objects, such as tables, stored procedures, etc. A Database Role is a group of principals, such as windows logins, sql server users, etc.

The idea is you can have a role of say "IT", and have all IT users under that role. Then you have can a schema called "IT", and have all tables that belong to IT under that. Out of the box SQL Server creates matching schemas for each default user and role in the database, but I think the intention is you customize this to match the needs of your organization.

This article has more information on the differences between owners and schemas. This question on Stack Overflow may also be useful.

2
votes

I am quoting the below from the following link.

https://msdn.microsoft.com/en-us/library/bb669061(v=vs.110).aspx

SQL Server ships with ten pre-defined schemas that have the same names as the built-in database users and roles. These exist mainly for backward compatibility. You can drop the schemas that have the same names as the fixed database roles if you do not need them. You cannot drop the following schemas:

  • dbo
  • guest
  • sys
  • INFORMATION_SCHEMA