Add user to sql server with the required permission

Question

In my sql server express 2008 r2 i have about 15 login account. Now i want some about 5 users have full permission while some other 5 users with limited access to some database, and other users only read permission. In future i may add other users also. So i was thinking of doing it in simple way than giving permission for every user as it requires giving permission to each database every time a new user is created. So can i create a set of rules or group and then add a new user to that group or rules? Do i have any kind of facility in sql server which will make this happen?

Answer 1

Use a database role. Database roles are database specific (obviously) so you can't create a role that grant's permissions to multiple databases at once. However within the database you create a role either through the GUI or using the command CREATE ROLE <rolename>. Once it is created you can then grant the role permissions just like you would a user. Then you add users to the role. Again you can either use the GUI or the command EXEC sp_addrolemember '<rolename>','<username>'. And last but not least there are some pre-defined roles at both the server and database level. (You can't create server roles in SQL 2008.) You can not make changes to the pre-defined roles but among others there is db_datareader which grants SELECT permission to every table in the database. Oh, and you can add your user defined role to the pre-defined role. So you could create a role MyReadOnlyRole and add it to db_datareader and then grant it EXECUTE permissions to various stored procedures, functions etc. Or EXECUTE permission to the database itself for that matter. (That gives permission to execute any SP, function etc in the database.)