Database objects are tables, indexes, views, triggers, and stored procedures. The user who creates a database object is the database object owner. The database owner or system administrator first must grant the user permission to create a particular type of object. The database object owner can then 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 implicitly granted all permissions but explicitly must grant permissions to other users before they can access the object.
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:
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 username John. If T1 is not qualified with the username 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 first be dropped or their ownership must be transferred to another user.
Note SQL Server allows all members of a role or Windows NT group to be specified as the owner of an object. For example, to create the table group_table owned by the Windows NT 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.
| Delimited Identifiers | sp_changeobjectowner |