Best SQL permissions for new (learning) users

I am trying to determine the appropriate premissions settings for new SQL users.

I have a SQL database that is serving as a small datawarehouse.  For the past year or so there have only been 2 people using it.  There are a few new people who are going to be learning SQL (first time users) who will be starting on the databse next week.  We do not have any system like Cognos or Business Objects...they are going to need to learn SQL.

I want them to be able to:

write select statements on database tables
create temp tables, and do anything with them (insert, update, delete)
create new stored procedures on any database
update existing stored procedures (I know this is a risk because they could delete stuff, but we do take backups...it will be more of a pain if we limit them from doing this)
etc...stuff like that...

What I want to AVOID is:

table drops
updates to existing database tables
deletes from existing database tables
changes to any indexes

what would be the best way to set permissions for these new users to they basically have full access to everything, but can't do any serious damage?
LVL 1
collagesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Since you mentioned that they are learning SQL and they are not proficient in SQL, I would suggest you to
1. Restore any one of the Existing backup to a Different DB in either the same server or separate server
2. Give full credentials to them on that database.
3. Dont grant any permission to your existing or production database.

Since they are learning, giving full privileges would help them compared to restricting with few rights.

Hope this makes sense.
If it doesn't revert so that I can provide you the appropriate required privileges but might affect some of their additional learning which is not forecasted.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
collagesAuthor Commented:
I think the challenge is I want to have these people working on actual projects with real time data.  A lot of the data is updated daily, and does change...

I'm really looking to give them full access to everything, except I do not want them to be able to delete or update non-temp tables.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.