SQL Server Schema Best Practices

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

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.

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

Processing your response...

Discuss This Question: 1 &nbspReply

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

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

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

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

To follow this tag...

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