Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SqlZ
Yak Posting Veteran
69 Posts |
 Posted - 2004-07-30 : 11:14:10
|
| I am sure this has been gone over one million times, and sorry to be repeatable if I am but is there any way to allow developers to create stored procedures as dbo without giving them dbo access. They need to create procedures as dbo.SPName but I don't want them to have access to make table changes etc. I have thought about a job to changeobjectowner to run a couple a times a day but then if they need to alter the procedure then they cannot. I am convinced I have to give them dbo access, please prove me wrong :)"DBAs are overpaid and their jobs are easy...heck, I could do it."-Quote from a former boss who calls often for help and speaks to my voice mail :) |
|
|
JasonGoff
Posting Yak Master
158 Posts |
Posted - 2004-07-30 : 11:39:00
|
| Why don't you want devs creating tables ? Is this on a production box ?Create a dev environment. Give developers DBO permissions in the dev database, get them to script their sprocs (inc permissions etc), and supply to the DBA for checking/promotion. |
 |
|
|
SqlZ
Yak Posting Veteran
69 Posts |
Posted - 2004-07-30 : 11:49:40
|
| Our situation is that we have a Modeling Group that creates the DBs, scripts the DDL to give to the DBAs, we run it and it should not be touched without direct involvement of the Modeler because they have signed off on it. I have argued the point that the code will blow up when migrated up the environment ladder because if a developer changes a table let say, that change won't be in the testing environment or production environment and the code will error upon promotion but they want the db locked down anyway. I am already convinced that there is no way to do this without giving developers dbo access in dev. |
|
|
|
JasonGoff
Posting Yak Master
158 Posts |
Posted - 2004-07-30 : 11:56:10
|
Demarcation, don't you love it ?quote:
I am already convinced that there is no way to do this without giving developers dbo access in dev.
Trust your developers. Create a process whereby they can request table amendments from Modeling Group prior to them making their code changes. The Modeling Group can then approve or reject the change and the developer would then have to live with it. |
|
|
|
SqlZ
Yak Posting Veteran
69 Posts |
Posted - 2004-07-30 : 12:03:21
|
| Thanks for your replies Jason. I agree with you, don't get me wrong. I meant in my previous reply that I believe its no big deal because if a developer should change something I will know when I promote it up the env ladder and then the developer will have to work with the data architect regarding the change (keep it or scrap it). If there was a middle ground to satisfy both developers and architects I would implement it, but in this case there is not because to create an object as dbo, you have to have dbo access. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-07-30 : 15:34:30
|
| You could give them ddladmin rights, but that allows them to create tables too. I think the best way to do this is as described above where they build their sprocs in an environment where they do have permissions, and script them for a DBA to produce them in higher level databases (QA, Production, etc.) That way the DBA can filter out any inappropriate changes such as table changes.-----------------------------------------------------Words of Wisdom from AjarnMark, owner of Infoneering |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-07-31 : 01:01:29
|
Hi, I have the same setup, what I did is I created an interface, wherein they can only edit stored procedures and functions. It's the only way if you want to limit your users to certain rights only.  You'll get negative reactions but they'll get used to it.quote:
Originally posted by SqlZ
I am sure this has been gone over one million times, and sorry to be repeatable if I am but is there any way to allow developers to create stored procedures as dbo without giving them dbo access. They need to create procedures as dbo.SPName but I don't want them to have access to make table changes etc. I have thought about a job to changeobjectowner to run a couple a times a day but then if they need to alter the procedure then they cannot. I am convinced I have to give them dbo access, please prove me wrong :)"DBAs are overpaid and their jobs are easy...heck, I could do it."-Quote from a former boss who calls often for help and speaks to my voice mail :)
|
|
|
|
|
|
|
|
|
Grant DBO or Do Not Grant DBO