Schema Sandbox

Question

Schema Sandbox

Woody-458823

Mr or Mrs. 500

Points: 515
More actions
September 28, 2007 at 12:09 pm

#181527

I am an Oracle DBA who was recently handed a lot of SQL Servers to administer. I have several users/developers (on SQL 2005) who would like to have the ability to create or delete their own tables and views. They also need to be able to grant permissions to others to access/view their data. In Oracle it was rather simple, each user automatically has their own schema and, with the correct authority, can grant various levels of permission to other users.
Can I set up a developer environment wherby each developer has their on little "world" and where other developers cannot view or change anything unless specifically granted permission to do so? What kinds of permissions do I need to give each of them?
😉

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

My first suggestion, take some time to read BOL (Books On-Line), specifically schemas. This will give you a good place to ask additional questions.

😎

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 2

Do you need all in one database? With SQL Server, it's easy, and you're licensed for developers to install their own edition of SQL Server on their own system.

If it's together, you can easily give the developers DDL admin, but they could change other views. You could create a schema for each developer and give them writes to build objects in their own schema.

Woody-458823 Mr or Mrs. 500 Points: 515 More actions · Answer 3

Lynn Pettis (9/28/2007)
My first suggestion, take some time to read BOL (Books On-Line), specifically schemas. This will give you a good place to ask additional questions.

And what would be your second suggestion? Sorry, I just could not resist a little sarcasm.

Ok, let me say I have read what I could find in BOL and did not find an answer to my question.

1) I have created a "Sandbox" database

2) I have created logins which map to Sandbox which only have "public"

3) I have created a shema for each login and made that login the owner

4) I have assigned the corresponding schema as the default for each login

All well and good so far...

Now, I need to make the proper grants so that each login can create tables and views in their schema and so each login can grant permissions on their schema/objects to other DB logins. I was hoping to do this with just the minimum amount of privileges necessary.

I did not find this specific information in BOL. If it is there, maybe you could point me to it.

Woody-458823 Mr or Mrs. 500 Points: 515 More actions · Answer 4

Steve Jones - Editor (9/28/2007)
Do you need all in one database? With SQL Server, it's easy, and you're licensed for developers to install their own edition of SQL Server on their own system.
If it's together, you can easily give the developers DDL admin, but they could change other views. You could create a schema for each developer and give them writes to build objects in their own schema.

Thanks Steve. To answer your questions: They would like it to be in one DB because they share a lot of data and would make it more convenient. They are also physically separated into various buildings. Also, I really don't want to manage multiple DB's for this. Some of them have installed their own SQL editions but would rather work jointly on the server.

I have taken a "schema" approach because I am familiar with it from the Oracle world. However, I'm not aware of the minimum privileges which I would have to grant in order to establish the kind of environment I envision.

Also, because I am new to SQL Server, I could be totally off base with my current approach. If there are some better methods of establishing a robust development arena, I would be most appreciative of any suggestions.