June 30, 2009 at 10:50 am
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?
June 30, 2009 at 10:57 am
Hi rsheppick,
Does the application execute any stored procedures or create any objects in the database?
regards
David
June 30, 2009 at 11:07 am
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.
June 30, 2009 at 11:11 am
Curious, what permissions were the developers using while building the app?
June 30, 2009 at 11:12 am
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
June 30, 2009 at 11:22 am
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.
June 30, 2009 at 11:25 am
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.
June 30, 2009 at 11:29 am
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.
June 30, 2009 at 11:30 am
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.
June 30, 2009 at 11:34 am
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
June 30, 2009 at 11:35 am
Have you granted your users execute permissions on the stored procedures?
June 30, 2009 at 11:35 am
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.
June 30, 2009 at 11:38 am
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
June 30, 2009 at 11:40 am
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.
June 30, 2009 at 11:44 am
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