August 22, 2005 at 9:51 am
Hello everyone,
We want to create a login (MyUser) on the server. We want to give acces to this login in database MyDb (an existing database) using the username MyUser.
We also want to create a schema named MyUser and have the user MyUser as the owner of this schema.
We are running those comands using the sa user.
Here are the commands we are using:
create login georoute with password = 'MyUser', default_database = MyDb
go
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 think that MyUser is the Owner of the MyUser schema...
But not... 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
August 22, 2005 at 1:22 pm
In my previous example I mistypes the login. You should have read:
create login 'MyUser' with password = 'MyUser', default_database = MyDb
go
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
Instead of login 'georoute'.
Sorry,
Carl
August 24, 2005 at 1:48 pm
No one tried this?
I would appreciate any feedback.
Regards,
Carl
August 25, 2005 at 12:32 am
Sorry, didn't see this earlier.
Which version are you using? I'll try it when I get home tonight (9 hours from now).
August 25, 2005 at 6:06 am
Hello Chris,
Tanx for your help.
We are using CTP of june or bêta 3 (the last one).
Best regards,
Carl
August 25, 2005 at 6:42 am
Sorry its bêta 2 not bêta 3 (mistype).
August 25, 2005 at 2:48 pm
Well, unfortunately I cannot say much more than I get the same result. But I have not really looked into the new features regarding schemas and principals yet, so I can not draw any real conclusions from it.
August 26, 2005 at 7:22 am
Ok thanks Chris.
Principals and schemas are new features but it is not well explain in BOL.
When we want to restrict privileges on only one schema for a principals, it seems very awkward. In our case we want to give "Take OwnerShip" and/or "Control" on that schema.
Is there any way I could tell Microsoft about that without having to give them my credit card number?
Best regards,
Carl
August 28, 2005 at 8:26 am
Absolutely, use the MSDN SQL Server forums. It is not an official channel, but there are lots of MS guys reading.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply