SQL Server offers three pretty simple commands to give and remove access, these commands are:
GRANT – gives a user permission to perform certain tasks on database objects
DENY – denies any access to a user to perform certain tasks on database objects
REVOKE – removes a grant or deny permission from a user on certain database objects
Here are some examples of these commands.
Allow users Joe and Mary to SELECT, INSERT and UPDATE data in table Customers
GRANT INSERT, UPDATE, SELECT ON Customers TO Joe, Mary
Revoke UPDATE access to table Customers for user Joe
REVOKE UPDATE ON Customers to Joe
DENY DELETE access to table Customers for user Joe and Mary
DENY DELETE ON Customers to Joe, Mary
As you can see from the above examples it is pretty easy to grant, deny and revoke access. In addition to grant SELECT, INSERT, DELETE and UPDATE rights you can also grant EXECUTE rights to run a stored procedure as follows:
GRANT EXEC ON uspInsertCustomers TO Joe
To determine what rights have been granted in a database use the sp_helprotect stored procedure.
In addition to granting rights to objects that you create you can also grant users permissions to do other tasks such as create tables, views, stored procedures, etc… To grant a user permissions to create a table you would run this command.
GRANT CREATE TABLE TO Joe
As you can see granting rights and permissions to certain features is not all that difficult to do. Take the time to understand what permissions are really needed by the database users and grant, deny and revoke accordingly instead of just using the default database roles.