TAKE OWNERSHIP and CONTROL permissions
-
Saturday, August 08, 2009 10:57 PMCan someone explain me difference between TAKE OWNERSHIP and CONTROL permission?
I am having trouble understanding what does it mean to be an owner, what exactly does it mean?
All Replies
-
Sunday, August 09, 2009 1:23 PMModerator TAKE OWNERSHIP means that you become the owner of the object. This permission is at a very granular level - an object within a database.
CONTROL is a more macro level permission that grants unlimited authority within its scope.
You grant CONTROL permission on a database or instance. You TAKE OWNERSHIP of a table, view, schema, etc.
At one level, there is essentially no difference. For example, any user with CONTROL permissions can perform the same actions that an object owner can. This is done through some implicit mappings to the database owner. The sysadmin role is automatically mapped to the database owner role in every database and you do not need to add any member of the sysadmin role as a user in any database, the same goes for any login with CONTROL SERVER permission. A member of the db_owner role is automatically an owner of every schema and every object within a schema without ever needing to grant permissions, the same goes for any user with CONTROL DATABASE permission.
However, it is possible for a user to own a single object - table, view, stored procedure, or function without having access to anything else. This is why there is essentially a "master switch" - CONTROL and a "detailed switch" TAKE OWNERSHIP.
Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals- Marked As Answer by Alex Feng (SQL)Moderator Friday, August 14, 2009 2:20 AM
-
Tuesday, August 11, 2009 11:44 PM This is a good question that I don't remember answering before in all the detail it deserves, so here's a lengthier answer around these topics:
http://blogs.msdn.com/lcris/archive/2009/08/11/basic-sql-server-security-concepts-ownership-control-take-ownership.aspx.
In a nutshell: ownership means you can do whatever you want with the owned entity. CONTROL is a permission that covers all others, but having CONTROL is not ownership and you can have CONTROL but be denied other permissions. TAKE OWNERSHIP is just a permission that allows one to become the owner of an entity.
Hope this helps
This post is provided "AS IS" with no warranties, and confers no rights.- Marked As Answer by Alex Feng (SQL)Moderator Friday, August 14, 2009 2:20 AM
-
Thursday, January 10, 2013 10:24 AM
>ownership means you can do whatever you want with the owned entity.
There is a higher level of ownership with GRANT option:
use [AdventureWorks2012];
GO GRANT TAKE OWNERSHIP ON [dbo].[Shift] TO [marys] WITH GRANT OPTION; GO
I thought that object ownership by database user was deemphasized starting with SQL Server 2005.
BOL: "Ownership chaining is very useful in managing permissions on a database, but it does assume that object owners anticipate the full consequences of every decision to grant permission on a securable. In the previous illustration, Mary owns most of the underlying objects of the July 2003 view. Because Mary has the right to make objects that she owns accessible to any other user, SQL Server behaves as though whenever Mary grants access to the first view in a chain, she has made a conscious decision to share the views and table it references. In real life, this might not be a valid assumption. Production databases are far more complex than the one in the illustration, and the permissions that regulate access to them rarely map perfectly to the administrative structures of the organizations that use them."
http://msdn.microsoft.com/en-us/library/ms188676(v=sql.105).aspx
Kalman Toth SQL 2008 GRAND SLAM
New Book: SQL Server 2012 Programming
- Edited by Kalman TothMicrosoft Community Contributor Friday, January 11, 2013 12:58 AM
-
Thursday, January 10, 2013 10:36 PM
-
Friday, January 11, 2013 4:30 PM
A couple more things.
The CONTROL permission can be granted repeatedly to many users or roles all being in effect at once. But the ownership of an object can only be one principal (a user or a role). The role could have multiple users though.
The TAKE OWNERSHIP permission doesn't let you do anything with an object right away. For example it doesn't give you SELECT permission on a table. You have to use the TAKE OWNERSHIP permission to execute the ALTER AUTHORIZATION statement on the object, and make yourself the object owner, in order to have any permissions.
Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
- Proposed As Answer by Kalman TothMicrosoft Community Contributor Friday, January 11, 2013 7:37 PM
.png)
