Database Object Owner
A user who creates a database object (a table, index, view, trigger, function, or stored procedure) is called a database object owner. Permission to create database objects must be given by the database owner or system administrator. However, after these permissions are granted, a database object owner can create an object and grant other users permission to use that object.
Database object owners have no special login IDs or passwords. The creator of a database object is granted all permissions implicitly but must give explicit permissions to other users before they can access the object.
Referencing database objects
When users access an object created by another user, the object should be qualified with the name of the object owner; otherwise, Microsoft® SQL Server™ may not know which object to use because there could be many objects of the same name owned by different users. If an object is not qualified with the object owner when it is referenced (for example, my_table instead of owner.my_table), SQL Server looks for an object in the database in the following order:
- Owned by the current user.
- Owned by dbo.
If the object is not found, an error is returned.
For example, user John is a member of the db_owner fixed database role, but not the sysadmin fixed server role, and creates table T1. All users, except John, who want to access T1 must qualify T1 with the user name John. If T1 is not qualified with the user name John, SQL Server first looks for a table named T1 owned by the current user and then owned by dbo. If the current user and dbo do not own a table named T1, an error is returned. If the current user or dbo owns another table named T1, the other table named T1, rather than John.T1, is used.
If a database object owner must be removed from a database, the owned objects must be dropped first or their ownership transferred to another user.
Note SQL Server allows a role or Microsoft Windows NT® 4.0 or Windows® 2000 group to be specified as the owner of an object. For example, to create the table group_table owned by the Windows NT 4.0 or Windows 2000 group LONDON\Users, specify [LONDON\Users].group_table as the qualified table name. All members of the LONDON\Users group have database object owner permissions on group_table.