sqlfriends (3/13/2013)...They may need to read and write and also execute stored procedures in the databases...

Typically when it comes to security you want to grant the least privileges that the user needs to do its work. If these 5 users are only doing read, write, and execute type of actions, then DB_OWNER seems excessive. If the databases have schemas then a handy trick is to use permissions at the schema level instead of each individual object, for example:

GRANT SELECT ON SCHEMA::[schemaname] TO [user or rolename]GRANT INSERT ON SCHEMA::[schemaname] TO [user or rolename]GRANT UPDATE ON SCHEMA::[schemaname] TO [user or rolename]GRANT DELETE ON SCHEMA::[schemaname] TO [user or rolename]GRANT EXECUTE ON SCHEMA::[schemaname] TO [user or rolename]

The first one shows any user roles (db_owner, db_reader, db_writer .....)The second shows any stored procedures.

The way I prefer to do permissions is with SP's being the only way to access the data preferably but if adhoc queries are going to be run from the app give data reader/datawriter (if updates need to be done out side of SP's which personally i never like).

If i want to go and create a custom level of access i prefer creating a custom DB role for the application put the application user in that role then assign all needed permissions to the role not the user. The benifit of this is that if you want a second user for the application for some reason (or a second app that has the same level of access but a different user) it makes things easier.

EDIT: You should be able to work with the sys.database_permissions table to get any thing else you may need.

sqlfriends (3/14/2013)For my case, do you think if I use above grant to schema permissions, it is better to create a customized role first?

Thanks

My preferred method is to use Roles (OR groups in AD) to assign permissions instead of assigning to individual users. Then you document what permissions the role has and if you forget to document adding a user to the role its easy to look at which user is assigned to each DB Role with a simple query.

For the best way as always it depends on the environment and the people at the top.

I like to use Database Roles and only grant permissions to the Roles even if the Role will only have one member initially. Consider that if a person leaves an organization you may delete their Login and Database User but the Role will still be there with the permissions granted to it and the person's replacement can easily be added to that Role and have the same permissions their predecessor had. That is just one benefit of using Roles, there are numerous and there really is no reason not to use them and avoid granting permissions directly to a User.

__________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --Plato