db_owner

Question

Post reply

db_owner

rsheppick

SSC Rookie

Points: 37
More actions
June 30, 2009 at 10:50 am

#133895

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?

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

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

TerrenceTheCat Ten Centuries Points: 1259 More actions · Answer 1

Hi rsheppick,

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

regards

David

rsheppick SSC Rookie Points: 37 More actions · Answer 2

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

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

rsheppick SSC Rookie Points: 37 More actions · Answer 5

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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 SSC Rookie Points: 37 More actions · Answer 7

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.

rsheppick SSC Rookie Points: 37 More actions · Answer 8

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.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 9

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

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

rsheppick SSC Rookie Points: 37 More actions · Answer 11

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.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 12

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

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.