SQL Server Schema Best Practices

Tags:
SQL
SQL Server
I am in the process of establishing a "sandbox" database for a mix of users with SQL development skills. I have done this before with Oracle but not with SQL Server. What I would like to do is build a database whereby each user has their own play area. We are running SQL Server 2005 so I thought of using Schemas to do this. My plan is to create a schema named for each user and to make that user the owner. Because I am still not knowledgeable of SQL Server permissions, I am curious as to how to establish an environment whereby each user has total control of their schema and can grant or revoke privileges to their objects at the schema level or a more granular level. Is this possible? Have you any suggestions as to best practices for establishing the environment I am trying to construct?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Yes you can do this within SQL Server. Make the user the owner of the schema, and grant them the control right over the schema. This will give them the ability to grant other users rights to objects within there schema, as well as give them the ability to create objects.

You can also create a seperate database for each user. If you give them each there own database they each have there own physical files so you can more easily keep track of who is using the disk space. To grant a user rights to the database, simply make them the owner of the database.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following