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 |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2004-08-29 : 22:33:33
|
| How can I give someone the ability to create procedures as dbo.stored_procedure_name and not give them the ability to create tables, rules and views? I've been experimenting with db_datareader, db_datawriter, db_ddladmin and db_securityadmin, but the combination gives too many permissions. I've denied Create Table, Create Rule and Create View, but the user id still has the ability to alter tables, create indexes and a few other non-desireable tasks.Thanks, Dave |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-08-30 : 01:08:36
|
| Hi Dave,I had the same problem but I think that's not possible.What I did is that I created a small program that will allow the users to create/alter sprocs and udf's but not other objects. |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-30 : 08:14:39
|
| GRANT CREATE PROCEDURE?A better question would be....why???MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-08-31 : 07:05:40
|
| In our case, we allow programmers to modify/create stored procedures with regards to reports. Our setup demands this permission set to programmers unless you want to work 24x7. Unfortunately for me, I'm the lone DBA. So to solve nagging programmers to modify/create procedures, I created an application that will allow them to modify stored procedures/udf's. I also imposed a set of restrictions to the creation of stored procedure, it should not be owned by dbo, only select from tables/views is allowed, and stuff that will prevent them from doing something disastrous. Ofcourse coupled with an auditing mechanism that is unkown to them. So the following day, I send myself a mail covering the activities of yesterday. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-31 : 12:58:30
|
| Is this in a production environment? If so, why would developers get any permissions in production. In development, they should get db_owner role.Tara |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2004-09-02 : 13:30:17
|
| Tara,This is for development. DBO is not working because the developers continue to create their own tables and in our environment that should only be done by the DBAs.Thanks, Dave |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-02 : 13:39:03
|
| And don't see why developers should be prevented from creating tables in a development environment. The DBAs should review the design that they are creating and make suggestions. You aren't going to be able to have the developers create dbo stored procedures without granting them db_owner role. They can create them under their own name if you grant CREATE PROC, but then you'll have to change the owner to dbo. In our environments, we grant db_owner role to all developers in the development environment. We review all changes that they make before we deploy to the QA environment then to production. Towards the end of the development cycle for an application, we freeze the schema. Which means no one should touch it, but they still have access to do it. So it's an education thing. If any changes need to occur during the freeze, then only the DBA would do it but still the developer would have access to do it.Tara |
|
|
|
|
|
|
|
|
Allow Create Proc (dbo.name), but little else???