September 20, 2010 at 1:16 pm
Hello:
I have a SQL Server 2005 database with an Access 2003 ADP front end, and I'm having some issues figuring out the proper security configuration.
The users need to be able to read and write data as well as access views and run functions and stored procedures. I thought I could accomplish this by assigning each login to the "public" server role, and then adding each user to the "db_owner" database role. To me, that setup would indicate that the users would have full access to the specified database, but not to any of the others on that server (unless I explicitly granted it).
I must not understand this accurately, because the users get a runtime error when they try to access the database through the ADP front end. The only way I've found around this is to assign the logins to the "sysadmin" server role, which I'm pretty sure I don't want to do.
Can anyone explain how I should be setting this up or point me to some documentation that might help me? I've read the BOL documentation, but I haven't found anything to explain why my setup is incorrect.
Thanks!
September 20, 2010 at 7:01 pm
Can you post the error?
The security best practices would say that you would create a specific role that has the needed permissions and assign each of those users to that role.
db_owner role gives more rights than needed.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 21, 2010 at 8:21 am
Hi, Jack:
Thanks for the response! I'm waiting for one of the users to get back to me with the exact error (all I've gotten from them so far was "a run-time error and then a bunch of numbers"). I'm unable to duplicate the issue, probably because I'm a domain admin.
As for creating a more restrictive role than db_owner, I've been playing around with creating a db_executor role that will encompass db_datareader and db_datawriter, as well as allow the users to execute stored procedures and functions. I just want to get to the point where db_owner works correctly, then I'll narrow things down from there.
I'll post the error as soon as I receive it.
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply