5

I have been working as a developer with SQL Server for years without asking the question, "what is dbo", a shame. Recently I have been studying the security model of SQL Server 2008. I have been overwhelmed by lots of concepts. Please clarify some concepts for me, and correct me where I am wrong. I don't need a thorough explanation on everything, just an idea what does what and the relationships involved:

LOGIN:  Server level (SQL Server login or Windows login).
USER:   Database level (a user is mapped to a login).
ROLE:   Users belong to a role. Can be at server level or at database level
        with permissions attached.
SCHEMA: Database objects belong to a schema (or dbo by default)
PERMISSION:
        For database objects or schema. A user or a role has permissions for
        an object or a schema.
OWNER:  Is it the user who created a object or schema? What does it do?
        How does it work? Where I can see the owner of a object in SSMS?
        I see only schema.object
1

1 Answer 1

4

An owner is a database principal that has ownership of a particular object, giving that principal full rights to the object(including ALTER, SELECT, INSERT, UPDATE, and DELETE). This principal can either be a database user or schema, depending on the context in which the object was created and the permissions of the principal that created the object. In the case of no explicit object ownership, the object is owned by the related schema owner.

There is also the db_owner role which exists in each database. Members of this role are considered to have ownership of the database and all objects within that database. Essentially, members of the db_owner role have "sysadmin/sa" rights in that specific database.

'dbo' is a default database user that is made a member of the db_owner role. Additionally, the 'dbo' schema is also created and used as default schema to create objects within. This schema is owned by the 'dbo' user.

To view object ownership, you can use the following query against the DMVs:

select
    SCHEMA_NAME(s.schema_id)+'.'+OBJECT_name(o.object_id) [ObjectName]
    ,u.name [ObjectOwner]
    ,so.name [SchemaOwner]
from
    sys.objects o
    left join sys.schemas s on (o.schema_id = s.schema_id)
    left join sys.database_principals so on (s.principal_id = so.principal_id)
    left join sys.database_principals u on (o.principal_id = u.principal_id)

For more detail, you can read Aaron's link or this article from Kathi Kellenberger.

8
  • Out of curiosity, do you have any databases where the middle column doesn't come up NULL? If you do, were these upgraded from SQL Server 2000 at some point? I checked a quick inventory across several servers and didn't find a single row... Dec 13, 2012 at 19:03
  • I tested and unless the ownership is explicitly declared, the middle column will be null. Example: ALTER AUTHORIZATION ON foo TO bar where bar is a user would have data in that column. Most cases, ownership defaults to the schema owner. So to answer your question, most of my databases also have that as null.
    – Mike Fal
    Dec 13, 2012 at 19:07
  • Interesting, I wonder if that happens often in real life (and again when a database wasn't brought forth from 2000). And I wonder how you change it back once you've altered it... Dec 13, 2012 at 19:10
  • To change it back, you'd use ALTER AUTHORIZATION ON foo TO SCHEMA OWNER. Not that I do much of this. I haven't seen a situation where I needed an explicit object owner.
    – Mike Fal
    Dec 13, 2012 at 19:14
  • 1
    Yeah clearly I don't do a lot of this either. Security is usually someone else's problem. :-) Dec 13, 2012 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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