Permissions in sql server 2000 (URGENT)

Question

Permissions in sql server 2000 (URGENT)

Sheilesh Paliwal

SSC-Addicted

Points: 425
More actions
July 26, 2004 at 3:09 am

#108753

Win - 2000
Sql server 2000 SP3
Hi Gurus,
I want to give developrs group of (50 developrs) following permissions -
Data reader,
Data writer,
Stored procedure , user defined functions create/alter /execute.
No table / constraint create/alter rights needs to be given
I appreciate if anybody can suggest a simple way to achive this , of-course the alternate ways also.
Thanks,
Sheilesh

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Santhosh Kumar Right there with Babe Points: 766 More actions · Answer 1

Hi Shilesh,

The better way is to create a role containing all the privileges u want and assign to the users.Which will be handy if u want to modify or remove any new privileges in future also.

With Smiles

Santhose

Robbac Ten Centuries Points: 1090 More actions · Answer 2

Hi!

Are the developers in a nt-group or individual logins (sql or nt)?

To be able to create or alter userdefined functions, users need to be, at least, db_ddladmin.

robbac
___the truth is out there___

Sheilesh Paliwal SSC-Addicted Points: 425 More actions · Answer 3

HI robbac,

yes, Users are under NT group.AppDev How use ddl_admin to create/alter/execute procedure/functions and not to create /alter table/constraints/indexes.

Thanks,

Sheilesh

Robbac Ten Centuries Points: 1090 More actions · Answer 4

You can't.

If a user has ddl_admin-rights, they are able to alter existing tables as well as other objects.

robbac
___the truth is out there___

Sheilesh Paliwal SSC-Addicted Points: 425 More actions · Answer 5

Oh, robbac, currently we have given db_owner to user group and from the database permissions revoked all "create table" and other rights except "crete procedure" and "create function". db_owner is a must as we have used "exec dbo.spname" while calling sp every where in the app, we are fine with this but somehow we want to revoke alter table also. Any workaround for this requirement is greatly appreciated.

Thanks,

Sheilesh

Sheilesh Paliwal SSC-Addicted Points: 425 More actions · Answer 6

Hi santhose,

Can a role can be created for the following requirement , if yes How ?

Users are under NT group named AppDev I have to grant create/alter/execute procedure/functions and revoke create /alter table/constraints/indexes.

Thanks,

Sheilesh

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 7

the only way i can see to do this is by seperating your stored procedures off into a seperate database.

you can then drant DDL to your developers for the "test" database and put them in data_readers/Data_writers for the "tables" database

you might even find that this makes your developers use the proper 4 part naming scheme.

what you could also do is set up a job to update the stored procedures from "test" to "live" after your developers make changes

MVDBA

Sheilesh Paliwal SSC-Addicted Points: 425 More actions · Answer 8

Mike Vessey,

That can be done but that will be very difficult to manage, any other option pl. in same database itself.

Thanks,

Sheilesh

Sheilesh Paliwal SSC-Addicted Points: 425 More actions · Answer 9

Mike Vessey,

That can be done but that will be very difficult to manage, any other option pl. in same database itself.

Thanks,

Sheilesh

John Jakob SSCommitted Points: 1720 More actions · Answer 10

Yeah -- Have to do this thing all the time here.

Basically, like someone said above, just create a role.

Add your NT group ("AppDev" or whatever) to that role. Then grant perms to the role. It's just a few clicks of the mouse to give the role "db_datareader" and "db_datawriter" permissions. And you can write a short script to make it faster to grant permissions to >> ALL << stored procedures or all UDFs. If your databases are like ours here, they probably have TONS of stored procedures, so you certainly don't want to have to go clicking the box on each one!

For example, here's a script I regularly use to grant role "ro_SPExecutor" perms. to all stored procedures in a database:

SET QUOTED_IDENTIFIER OFF

GO

SELECT "GRANT EXEC ON " + name + " TO ro_SPExecutor" + CHAR(13) + CHAR(10) + "GO" + CHAR(13) + CHAR(10)

FROM sysobjects

WHERE xtype = 'P' AND name NOT LIKE 'dt_%'

ORDER BY 1

Run this in SQL Query Analyzer, then cut and paste the output into another window and run it.

Note that one can alter the "xtype" condition in the WHERE-clause to pull in UDFs also...

Hope this helps.

- john

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 11

this won't let them modify the SPs though - only execute

MVDBA

John Jakob SSCommitted Points: 1720 More actions · Answer 12

>>this won't let them modify the SPs though - only execute

Hey yeah, you're right -- sorry I didn't see in the original message that they wanted to be able to create/edit stored procs. as well.

So you'd have to do some additional granting of perms to allow that.

- john

Sheilesh Paliwal SSC-Addicted Points: 425 More actions · Answer 13

Hi John / mike,

Thanks for the beautiful solution. I have already tried the similar kind like data reader, data writer, create sp at database property level,and execute sp by using a generic stored proc.This solution will definitly help but in our case its not.

The problems is there are more then 1700 sps in database and they are called by "dbo.spname" for 8 different production databases same app code vb/asp/.net code is running.

and currently every user/ group is having db_owner role.If i revoke this db_owner role new sp creation will be of "user.spanme" not the dbo, in that case our app code will fail.

Any suggestion for this situation is greatly appreciated.

TIA,

Sheilesh

John Jakob SSCommitted Points: 1720 More actions · Answer 14

Well, sounds like there's going to be a lot of administration work in any scenario...

Have you considered multiple environments?

In our env. we have a "development" instance where developers can create/edit SPs all they want -- here you could make all users "db_owner".

Then on a (separate) "production" instance you could lock things down tighter -- perhaps allowing developers only the rights to EXECUTE stored procs. You (as DBA) would be the sole DBO there -- and developers would need to contact you whenver they wanted to move obejcts up to Production from Development...

[If are tight on $$$ and have SQL 2000, you can install a new named instance on a single box]

Just an idea.

- john