Post reply

Permissions in sql server 2000 (URGENT)

  • 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

     

     

     

     

     

     

  • July 26, 2004 at 3:40 am

    #516047

    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

  • July 26, 2004 at 3:51 am

    #516048

    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___

  • July 26, 2004 at 6:23 am

    #516058

    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

     

     

     

     

     

  • July 26, 2004 at 6:26 am

    #516059

    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___

  • July 26, 2004 at 6:48 am

    #516061

    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

     

  • July 26, 2004 at 6:51 am

    #516063

    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

     

     

  • July 26, 2004 at 7:12 am

    #516068

    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

  • July 26, 2004 at 7:25 am

    #516074

    Mike Vessey,

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

     

    Thanks,

    Sheilesh

  • July 26, 2004 at 7:25 am

    #516072

    Mike Vessey,

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

     

    Thanks,

    Sheilesh

  • July 27, 2004 at 9:14 am

    #516330

    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

     

  • July 27, 2004 at 10:18 am

    #516348

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

    MVDBA

  • July 27, 2004 at 5:13 pm

    #516430

    >>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

  • July 27, 2004 at 11:18 pm

    #516464

    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

     

     

     

  • July 28, 2004 at 5:34 pm

    #516673

    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

     

     

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

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