I realized today that while I have discussed earlier object permissions, I have not gone into the details of object ownership. I want to cover the following here: ownership of objects, how it can be changed, and the relatively new permission CONTROL (introduced in SQL Server 2005).
Ownership: This should be pretty clear – the owner of an object is usually its creator, but a different owner can be specified at creation time using the AUTHORIZATION clause. The owner of an object has all possible permissions on that object and, most importantly, cannot be denied those permissions while he continues to be an owner.
CONTROL permission: The CONTROL permission can be used to easily grant all permissions on an entity to some principal. It’s the next best thing after ownership of the entity, but it’s not quite as powerful as ownership. The main difference is that a grantee of CONTROL can still be denied some other permissions on the entity. For example, I can be granted CONTROL on a table, while at the same time I can be denied SELECT on that table, preventing me from selecting from it – this can never happen to the owner, because the owner cannot be granted or denied permissions.
Changing ownership: To change the owner of an entity, we can use the ALTER AUTHORIZATION statement. Changing ownership of an object creates some interesting scenarios in terms of what should be allowed and what should not be. Basically, owners should not be able to “dump” their objects to other users who may not want them and users should not be able to “steal” objects from those that own them. To accomplish this, two checks are needed: one controls to whom ownership can be given to – we verify that the performer of the change has some right on the new owner (we check for IMPERSONATE for users and logins, ALTER or membership for roles, etc); the other check controls from whom ownership can be taken away: we require the performer of the change to have TAKE OWNERSHIP permission on the object.
Note as a corollary that a grantee of CONTROL permission on an object has the ability to take ownership of the object because CONTROL implies the TAKE OWNERSHIP permission as well – the way to prevent this would be to grant CONTROL and deny TAKE OWNERSHIP.
TAKE OWNERSHIP is thus used to selectively grant someone the ability to willingly become the owner of an object or to facilitate the change of ownership of that object.
Side effect of ownership change: A potentially surprising side effect of changing ownership of an object is that all permissions granted on that object will be lost. As always, it’s a good idea to script all permissions granted on an entity before changing its ownership, so that the grants can be re-executed by the new owner, if appropriate.