Welcome Message

"The difference between a successful person and others is not a lack of strength,
not a lack of knowledge, but rather a lack in will."
-Vince Lombardi

November 23, 2009

SQL DBO SQL Server Database Object Owner dbo

Who is "dbo?

There are two "dbo" in SQL Server, one is Database Owner and one is Database Object Owner, dbo is a user with permissions to perform all activities in a database. Each database has this special user called dbo, user dbo cannot be deleted and is always present in every database. Database owners and object owners have no separate logins the user dbo is part of sysadmin fixed role or db_owner fixed database role. Any objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo.

Objects created by any other user who is not also a member of the sysadmin fixed server role including members of the db_owner fixed database role belong to the user creating the object, not dbo. The creator of an object is granted all permissions implicitly but must give explicit permissions to other users before they can access the object. When accessing objects an owner name should be qualified before object name like "object_owner.object_name"

SELECT * FROM Object_Owner.Table_Name

If object owner is not specified when referencing the object then SQL Server looks for objects owned by current user and then objects owned by dbo, if the object is not owned by current user or dbo then object owner must be specified other wise an error will occur. Windows NT users and groups can be owner of objects. If you want to remove a database owner from current database the objects owned must be dropped or ownership must be changed.

Check these two stored procedures in books online.

sp_changeobjectowner
sp_changedbowner

 

No comments:

Post a Comment

Thank you for your valuable comments.