Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

In most of my databases I create, I need to give permissions to do the following:

  1. SELECT/INSERT/UPDATE/DELETE on all tables in the database
  2. Execute all (user) stored procedures in the database

Will giving users datareader and datawriter be enough for the sproc's or what other roles/permissions do I need to give?

If I need to do more than just datareader and datawriter, could you point me in the right direction to creating a script to affect all users in the database.

Thanks in advance

share|improve this question

2 Answers

up vote 4 down vote accepted

With (1) you should be good giving them db_datareader and db_datawriter.

On (2) I would probably create a role and then grant that role execute permissions on all the stored procedures, in place of granting it to each individual user. Then just add those users to the role. If you have all the stored procedures under a schema name you should be able to grant execute to that schema. That will save time and not have to worry about new procedures being added to the database.

share|improve this answer
I like Shawn's comment. I would actually go with the role or schema level permissions for the read/write as well. It is still one less thing to do. I try and assign NO permissions to any one DB user but always go to the DB Role or schema level. – Mike Walsh Nov 10 '11 at 16:55
I also try and explicitly grant the objects they have read/write/update/delete/execute on. This is because I am a fan of least privilege and over time you may be too. It is easier moving forward for your developers to maintain the permissions script every time a new object is added and it requires permissions, that initial hurdle is a bit more difficult but can be scripted. That way if you have some new role that shouldn't read from n tables or execute on x, y or z proc, you can be a bit more granular. But definite +1 on this answer. – Mike Walsh Nov 10 '11 at 16:57
Thanks for the answer :) I am going to try this in a bit. Would you also recommend doing a Deny All then explicitly allowing the permissions you want, eg. SELECT/INSERT/UPDATE – Stuart Blackler Nov 10 '11 at 17:29
DENY ALL actually shows to be on the depreciated list in BOL. It is preferred to DENY only on specific objects instead. If you create roles for each permissions level you will need (include Mike's comments), you should not really need to DENY anything. I'm weary of using DENY to much cause it can be a pain to troubleshoot permissions down the road. If you setup the roles and permissions correctly you should not need DENY. – Shawn Melton Nov 10 '11 at 18:52
up vote 2 down vote

In addition to Shawn's answer and your comment..

Tables that are maintained by stored procedures or views only do not require any explicit permissions. "Ownership chaining2 states they are not checked: this includes a DENY.

I do prefer schemas though but I also disallow direct table access. For a given schema (Desktop, WebGUI etc) then I assume all clients with permissions need to use it. Within that, I'd have some application security too though eg based on SUSER_SNAME() or such to track users.

share|improve this answer
Thanks. I think I understand. I am going to test this and post back if I have any problems :) – Stuart Blackler Nov 10 '11 at 19:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.