Schema and principals (Sql Server 2005)
Hello Everyone,
There is something we don't understand on SQL Server 2005.
We want to grant acces to the login MyUser in the existing database MyDb. We want this login to be known as MyUser in MyDB and being the owner of a new schema named MyUser.
Here are the commands we are using:
use MyDb
go
create user MyUser for login MyUser with default_schema = MyUser
go
create schema MyUser
go
grant Take ownership on schema::MyUser to MyUser
go
After that we would have tought that Myuser is the owner of the MyUser schema. But it is not the case:
Here is a select on sys.schemas:
Select * from sys.schemas
name schema_id principal_id
- --
dbo 1 1
guest 2 2
INFORMATION_SCHEMA 3 3
sys 4 4
MyUser 5 1
(6 row(s) affected)
The owner of the schema MyUser is the principal 1 which is DBO.
Whats wrong with all that?
Regards,
Carl
you need to use ALTER AUTHORIZATION statement to transfer schema ownership. TAKE OWNERSHIP permission merely allows grantee to become the owner of the object by calling ALTER AUTHORIZATION.
Thank's Ruslan,
But don't you think its weird.... You tell me that this command:
grant Take ownership on schema::MyUser to MyUserJust give MyUser (principals) the ability to become the owner of the object when I will call the following command:
alter
authorization on schema::MyUser to MyUser
Can someone explain me why the first command is not enougth to have MyUser the owner of the MyUser schema? Why the need for two commands? When do I need to issue just the first one knowing that it allows nothing to MyUser?
Tanx a lot,
Carl
I'd just like to point out that in your script, if the intention was to have MyUser own the schema, you could have just used the AUTHORIZATION clause in CREATE SCHEMA:
CREATE SCHEMA MyUser AUTHORIZATION MyUser
For your question, you don't need to issue 2 commands in your scenario, only ALTER AUTHORIZATION is required for what you want to do. GRANT TAKE OWNERSHIP is only granting a permission, nothing else, it does not change the ownership.
Why is the TAKE OWNERSHIP permission useful? As a user, I want to have control over the objects that I own. I wouldn't want any user to be able to make me the owner of their objects. So the TAKE OWNERSHIP permission was introduced as a means of providing control over this change of ownership. Let's say Alice has a procedure that she wants Bob to own. Alice does not have sufficient privileges to make Bob own the procedure directly via ALTER AUTHORIZATION (she would need IMPERSONATE on Bob), so what she can do is grant TAKE OWNERSHIP on the procedure to Bob and then let Bob know that he can become the owner. If Bob agrees, then he can use the ALTER AUTHORIZATION statement to make himself the owner. This way, the transfer is done if both parts agree. Bob cannot become the owner unless Alice granted him the TAKE OWNERSHIP privilege, and Alice cannot make Bob the owner unless Bob takes ownership or lets Alice impersonate him.
Thanks
Laurentiu
Thank's a lot Laurentiu,
Now its really clear. I understand the distinction between those 2 commands.
But it leaves me with 2 other question/remarks:
1- The AUTHORIZATION clause of the CREATE SCHEMA command is there only for backward compatibility as stated in BOL: "The AUTHORIZATION clause is included for backward-compatibility only."
For that reason we would like to get the rigth command not one that will be deprecated soon.
2- If I don't use the AUTHORIZATION clause of the CREATE SCHEMA command and that I use the "alter authorization on schema::MyUser to MyUser" instead. What do I need else to be able (as use MyUser) to create object on that schema? I would have tougth that being the owner of that schema would be sufficient to create objects in that schema but it is not the case.
Regards,
Carl
1. AUTHORIZATION is not deprecated. This appears to have been an error in BOL. The copy that I have states the following:
"Statements that contain CREATE SCHEMA AUTHORIZATION but do not specify a name are permitted for backward compatibility only."
This refers to a specific syntax of CREATE SCHEMA where the name of the schema is not specified. The statement does not mean that the AUTHORIZATION clause is deprecated, so you can use it without fear that it is scheduled for deprecation.
2. It depends on the object that you are trying to create. For example, for a table, you need to have CREATE TABLE on the database and ALTER on the schema. For additional information, you can check the Permissions section of BOL for each statement.
Thanks
Laurentiu
Ok Thank's Laurentiu.
As you said (I will trust you), It must be an error in BOL.
Best regards,
Carl
Hello Laurentiu,
just a little clarification. When you wrote:
"For example, for a table, you need to have CREATE TABLE on the database and ALTER on the schema."
It is not entirely true. I don't need the Alter on the schema since I am the owner of that schema. I only need the create table permission in that database.
Best regards,
Carl
As an owner, you have all permissions on the schema, including ALTER. However, for creating a table you don't need to own the schema, you only need ALTER on it.
Thanks
