SQL 2k Roles for Developers - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL 2k Roles for Developers

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-19 : 10:57:14

Never had to know this before, but, is there a role to just allow sproc creation, or id DDLAdmin the only role (besides dbo) to do this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-12-19 : 11:51:21

in a roundabout way in 2005....why not write a trigger to rollback any inappropriate DDL...inappropraite from an authority point of view...(ie not in your "good" users list)

??

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-19 : 12:41:09

Yeah that's what I figured...never had to work that way

Give the developers a scratch pad dev box

Release code to me to place in QA (after assisting them in writing the code...also all database changes are stored in ERWin, so if the get cute their code would explode in QA)

Promote to Prod after sign off

I wonder what this guy is doing..I better ask

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-19 : 12:43:37

If you use DDLAdmin, the developers won't be able to create dbo objects. The only way for them to create dbo objects is for them to be members of the db_owner database role. We grant db_owner to all developers in the development environment only. They have read access (and sometimes write access) everywhere else.

Tara Kizer

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-19 : 12:54:23

Yeah, thanks...that's the way I've always done it...I really have to find out what they are trying to do...doesn't makwe sense

In OS/390 DB2 land it's a little different

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-20 : 16:28:00

OK, what the hell is this? I tried it and it creates the sprocs as dbo...unless I'm confused again or am missing something

quote:

Database Properties (Permissions Tab)
Use this tab to view or specify the following options.

Options
User/Role

View the name of the user or role.

Create Table

Specify whether permission to create a table is granted for each user or role.

Create View

Specify whether permission to create a view is granted for each user or role.

Create SP

Specify whether permission to create a system procedure is granted for each user or role.
Create Default

Specify whether permission to create a default is granted for each user or role.

Create Rule

Specify whether permission to create a rule is granted for each user or role.

Create Function

Specify whether permission to create a function is granted for each user or role.

Backup DB

Specify whether permission to create a backup database is granted for each user or role.

Backup Log

Specify whether permission to create a backup log is granted for each user or role.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-20 : 16:32:02

What permissions does the user have that created the dbo sproc?

Tara Kizer

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-21 : 10:02:49

I thought I set up a windows connection for my lan id that was public only...I'll have to 2x check...but I looked already at th permissions and it had nothing to northwind when I added it to that "group"?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-21 : 11:39:04

Well you probably have local admin on the box, which means sysadmin...

We tried everything to get it to work without db_owner. So if you can find a way, we'd be interested.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources