August 30, 2004 at 1:12 pm
Hello...
I'm a 50/50 C#\TSQL guy who is moving more and more into a DBA role.
On my last contract where I was essentially "everything", I did not worry about any "shared environments" or permissions. My local machine WAS the Dev box and that was that(and I was dbo and sysAdmin as well).
Now, if I'm a DBA but want to allow other SQL developers create their own new tables but not edit the existing ones - how is this best accomplished in a shared environment?
Ideally, we'd have a DEV database on a public server in which all developers could create their own new tables and SProcs, but the core tables, SProcs etc would remain unchanged.
Any simple suggestions ?
- B
August 30, 2004 at 1:28 pm
Simple suggestion:
The main objects (that are not to be changed) are to be owned by DBO and only created by you, the DBA.
Each developer can have the permissions to create objects under their own username (e.g. username.tablename). Once they're done with the iterative development of their DB objects, they submit to you the DBA the tested DDL creation script.
You run it as DBO, and now this object is part of the main line of development.
Does this suit your needs? I have a couple other ideas if this does not apply to your scenario.
Cheers,
-- Mark K.
August 30, 2004 at 1:33 pm
Mark - thanks.
That's pretty much what I'm after.
Is it possible to grant permissions to do things like CREATE TABLE or CREATE PROCEDURE without having the user be a sysAdmin? There must be a way - I just need to dig into the books a bit.
- B
August 30, 2004 at 1:41 pm
Yes, there is a way.
Without a user being SysAdmin or anything, you can grant them CREATE permissions. For exampe:
GRANT CREATE TABLE to <username>
Now have said that, if you will be dealing with a large number (or often changing) developers, roles would be a better and generally more elegant solution.
1. Create a role called , say, devrole
2. GRANT this role all the permissions you deem necessary for a developer. (replace <username> with <rolename> in syntax above)
3. Add and remove developers from this role as necessary.
A further benefit is if you decide later to add or remove some permission, you can simply do it for the role and all developers will be thus modified.
Check BOL (SQL Server Books OnLine) for full syntax and such.
-- Mark K.
August 30, 2004 at 1:45 pm
Thanks again.
Reading the BOL now and I do see what I'm looking for.
Quick question - assuming I create a role and give it CREATE TABLE rights - if a developer does create a table - do they full access to it from there? TO edit the table and such?
Regardless thanks again - this is a good site with good people.
- B
August 30, 2004 at 9:18 pm
When the developer creates the table, he/she would be the owner of the table. The owner of the table has full control over the table. If you want to restrict the access, you can change the ownership of the table(as Mark Suggested) by running sp_changeobjectowner procedure.
August 31, 2004 at 1:15 am
Take a look at Roles based security in BOL. You may want to create a developers role, or you can use the existing ones.
If you add someone to the db_ddladmin role for a database, they can then add/change/delete objects (like tables/sprocs/indexes). Add them to the db_datareader and db_datawriter roles, and they can do anything with the data as well.
Julian Kuiters
juliankuiters.id.au
August 31, 2004 at 9:26 am
As far as a NON-sysAdmin who has CREATE, ALTER, DROP, etc., take a look at the DDL_Admin database role in BOL.
Corie Curcillo
MCT, MCDBA, MCSD
August 31, 2004 at 4:10 pm
Hi,
This solution would be too complex for me. As soon as you start using anything other than dbo as table owners in a development environment it all starts to get hard to manage, and you *will* have problems. Plus there is the "best practice" of referencing tables by owner.tablename...
I run a development shop with about a dozen developers. All developers have sysadmin access on our development boxes. This means they never have to stop developing because they don't have permissions. It also means they don't put in ugly workarounds because they don't have permissions. I would have to ask why allow create tables but not alter tables?
At completion of development, objects are scripted to qa. Developers do NOT have sys admin access to qa or production. We use SQLCompare (mmmmmm...SQLCompare) to make sure we pick up all the changes to existing objects. I run the scripts to QA, the developer tests in QA, and then a UAT person tests. Once this is done, the same scripts are used to push the objects to production.
Works for us.
Steve
September 1, 2004 at 7:05 am
Thanks all for the tips.
In response your question sdoughty - I did work at a shop in the past which had something setup similar to what I was referring to - only in Informix.
This way - the core(or existing) tables stayed static and all could test and use the data. However, new tables for new projects were created and edited only per developer so they didn't stomp on eachother or modify data they shouldn't - and it seemed to work OK.
Also, I beleive I've read that prefixing ALL objects and call with 'dbo.' (or the real owner) actually speeds up the process somehow (avoiding a user lookup perhaps or being able to cache more effeciently???)
However, I'm all for simplicity. Making all the developers sysAdmins will certainly save time and hassle. Let them handle how and when to edit data or structure.
Still thinking about it.
- B
September 1, 2004 at 11:52 am
In all of the shops I have been worked, the developers have sa rights on their development boxes. I don't care what they do there. I set all of them to simple recovery model and back them up once a week (Sunday night).
They are supposed to use Visual SourceSafe to check in their scripts. They do, mostly.
When they need to ship it, they send me the script of all of the changes to the database. I test it, then apply it to the staging box. The person who tests then does their magic. Then, if approved, I run the script on production.
The developers never have access to production.
Quand on parle du loup, on en voit la queue
September 1, 2004 at 12:43 pm
I tend to work as Patrick above, but if there is a shared server, usually developers don't have rights. Not to be a pain, but keep track of what's changed and make deployment easier. They complain at first, but quickly learn it's nice when they don't have to worry about the deployment package of db objects. I take responsibility for that.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply