Does login need be created to create a schema

Question

Does login need be created to create a schema

gary1

SSC-Insane

Points: 21152
More actions
December 29, 2010 at 11:38 am

#242818

Hi,
I need to create 2 schemas, schema1 and schema2 in database Mydb for two users User1 and User2.
For this, do we need to create the 2 logins for 2 users and then map the database Mydb and then create the Schema1 and assign to user1 & create Schema2 and assign to schema2?
How does this exactly works?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

You can create them in either sequence. Schema first or login first.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

gary1 SSC-Insane Points: 21152 More actions · Answer 2

So in either way login has be created right?

and what permissions need to be given to the login to create objects in that particular schema?

Lets say, I have created a login login1 and mapped that login to Mydb. Created a schema schema1 in Mydb and now I want to give rights to login1 to create objects in schema1.

Basically, I want that login1 to be the owner of that schema1 so that he can create all objects.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

GRANT CREATE TABLE ON SCHEMA::MySchema TO MyUser

You'd need to do that for PROCEDURE, VIEW, etc.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

GSquared SSC Guru Points: 260824 More actions · Answer 4

Keep in mind that making someone a schema owner can have unintended consequences security-wise. Check the MSDN/BOL articles on GRANT with regards to schemas.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 5

Perhaps you want to explain what you are trying to do in the database from a logical perspective, rather than saying you want a login to own a schema. What are you trying to accomplish business-wise?

gary1 SSC-Insane Points: 21152 More actions · Answer 6

Perhaps you want to explain what you are trying to do in the database from a logical perspective, rather than saying you want a login to own a schema. What are you trying to accomplish business-wise?

Ok..our users are using Oracle database and they have their own schemas in it to create objects and they get data from production using dblinks and then do their reporting/ queries in their own schema. Similar thing they want to have it in SQL Server . So we are now using SQL Server 2008 and I want provide the same in SQL Server for the users.

What is the best way to achieve this?

Thanks in advance

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 7

gmamata7 (12/29/2010)
Perhaps you want to explain what you are trying to do in the database from a logical perspective, rather than saying you want a login to own a schema. What are you trying to accomplish business-wise?
Ok..our users are using Oracle database and they have their own schemas in it to create objects and they get data from production using dblinks and then do their reporting/ queries in their own schema. Similar thing they want to have it in SQL Server . So we are now using SQL Server 2008 and I want provide the same in SQL Server for the users.
What is the best way to achieve this?
Thanks in advance

Ah, a schema in Oracle roughly translates to a database in SQL Server. So it sounds like the users are used to having private working areas where they can do whatever they want. That would be seperate databases in the SQL Server world, but, I'd drill down on what they're doing and why because, depending on the number of users, you sure don't want to have to maintain 50-100 seperate databases.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Pete23Mid SSC Enthusiast Points: 162 More actions · Answer 8

You can as well create databases / schemas for themselves and than change ownership to their logins. You can do it in any order you want, database first, or ligins first.

green smoke reviews

LutzM SSC Guru Points: 107049 More actions · Answer 9

Pete23Mid (12/30/2010)
You can as well create databases / schemas for themselves and than change ownership to their logins. You can do it in any order you want, database first, or ligins first.

Pete: Would you mind changing your signature?

Otherwise your post's will be consistantly reported as spam.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]