db_owner

  • Hey guys,

    I have provided and built a database for an in-house application here at my work. The front end application is being developed by another person.

    The development has gotten to the point where "regular users" need to test and provide feedback on the application.

    I have been provided an active directory group with those people in it, I added that group into the SQL Server with Windows Auth. I provided them db_reader,db_writer and they are defaulted to the correct database and mapped correctly.

    Oddly enough the front end application dies the second it starts up, I can resolve this by providing it the above group db_owner, however, I don't want to give them that.

    There is nothing that I have done in the database that would require their login to have owner rights, however the front end people cannot tell me why the application simply crashes unless it has it, meaning they cannot tell me what it is trying to do when it crashes, it appears that they have this problem regardless of trying to catch the exception.

    I'm at a total loss as to why they need owner rights.

    Anyone have any ideas?

  • Hi rsheppick,

    Does the application execute any stored procedures or create any objects in the database?

    regards

    David

  • David Nagle (6/30/2009)


    Hi rsheppick,

    Does the application execute any stored procedures or create any objects in the database?

    regards

    David

    It doesn't create any objects, triggers, temp tables ect.

    All of the sprocs simply return data, never creates it.

  • Curious, what permissions were the developers using while building the app?

  • Did you try running a profiler to find out what exactly the app is doing that it needs the db_owner? Then you could refine the rights according to what it does.

    -Roy

  • Lynn Pettis (6/30/2009)


    Curious, what permissions were the developers using while building the app?

    We had full god mode rights ha ha, so it never came up.

  • rsheppick (6/30/2009)


    Lynn Pettis (6/30/2009)


    Curious, what permissions were the developers using while building the app?

    We had full god mode rights ha ha, so it never came up.

    And that is probably your problem.

  • rsheppick (6/30/2009)


    Lynn Pettis (6/30/2009)


    Curious, what permissions were the developers using while building the app?

    We had full god mode rights ha ha, so it never came up.

    I did, i see it try to exec a sproc then the app gets returned to it access denied.

  • Lynn Pettis (6/30/2009)


    rsheppick (6/30/2009)


    Lynn Pettis (6/30/2009)


    Curious, what permissions were the developers using while building the app?

    We had full god mode rights ha ha, so it never came up.

    And that is probably your problem.

    Anyways, the problem does happen when it trys to run stored procedures.

  • db_reader and db_writer is not enough to execute stored procs. If all operation is through Stored procs, then you can as well remove those rights and give execute permission for the procs in question to the app user.

    -Roy

  • Have you granted your users execute permissions on the stored procedures?

  • I have provided the group the roles db_datareader and db_datawriter , I'm not sure what more it is that I need to give them and why it is.

  • db_datareader is to do Select queries directly on the table, and datawriter to modify. If it is stored procs, you have to give execute rights to the stored proc for the users.

    Grant execute on dbo.storedprocname to whateverappuseris

    -Roy

  • rsheppick (6/30/2009)


    I have provided the group the roles db_datareader and db_datawriter , I'm not sure what more it is that I need to give them and why it is.

    I may be wrong, but I don't think that those permissions give users permissions to execute stored procedures. I'd have have to test it to be sure.

    You may want to try it and see if it fixes your problem.

  • Just ran a test in my Sandbox database. A user with only db_reader and db_writer could not execute a stored procedure. I had to give the user db_owner privs or grant execute permissions to the stored proc for that user.

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

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