Post reply

Database owner with limited permission

  • January 20, 2004 at 2:50 am

    #160402

    Hi,

    Here is my case :

        I have created development databases for each  develoopment team in my Department and include every team members as database owners db_owner).several months later,I discovered that the developers have added about fifty users (application user)to thier databases for testing purposes while they should do this in user test database.They could also give any permission to any user as long as they are member of db_owner.

    When I tried to overcome this problem,I remove the developer accounts from db_owner role and add them to other fixed role such as db_datareader,db_datawriter and db_ddladmin.

    It works fine except that any object created by the user will be owned by him rathar than dbo.This will badly affect the application that access these objects and it's time consuming to change the owner manually.

    So,any idea about how to give the developer in the devlopment database the proper permission(without adding them to db_owner role) so that they couldn't add users and grant any permission to others .

    Also,I want All new objects must be created and owned by the dbo and other onwer is notacceptable unless it's a temperory table.

    Thanks in advance

  • January 20, 2004 at 10:26 am

    #490858

    As long as the developers are members of db_ddladmin, they can force their objects to be owned by dbo.

    For example, to create a new table:

    create table dbo.t1 (x int)

    Or, for an existing object:

    sp_changeobjectowner 'non_dbo_user.t1', 'dbo'

    Hope this helps!

    Chris

  • January 20, 2004 at 12:07 pm

    #490888

    I like Chris' suggestion and would use that.

  • January 20, 2004 at 12:22 pm

    #490891

    I am not sure why your db_owner can create logins...  I think this privilege can only be given to "sa".  I don't mind having db_owner giving user access to their database, but creating another user would be problemetic.  At where I am we have lots of DB_Owner and DBO but none of them are allow to create their own users regardless of which environment they are in.

     

    mom

  • January 21, 2004 at 6:26 am

    #490960

    Hi,

    The reason why I need to prevent the developers from adding new user to thier database is that the users should have access to the data only through user_test_db which is the testing area beside the production databases.All other database fixed roles such as db_ddladmin does not give the developer full permission aginst the object and he cann't create object with dbo

    owner.

    So, what I need is a role with full permission aganist all database objects as dbo and prevent the developer from adding any other users.

    thanks in advance  

Viewing 5 posts - 1 through 4 (of 4 total)

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