First the server and databases must have ordinary security implemented, using ANSI SQL conventions, since we dealing with ANSI SQL databases. You can have more security in your corporate security model, but you cannot have less that what is available via ANSI SQL. In this day and age we want databases that are Open (available to any client app) but secure (specific access available to specific users).
The old model uses Groups (limited to a single db); the current model uses Roles (server-wide), I am giving you Roles. This means:
a every person has a separate Login and password
b Logins are granted to any number of Roles
c the tables have SELECT/REFERENCE Granted to 'public' (any person added as an User can read all tables).
You might have a Role for SELECT/REFERENCE on all tables instead of to 'public'.
d In a very secure environment, SELECT/REFERENCE is Granted to specific Roles (only specific Logins can read).
e the tables have INSERT/UPDATE/DELETE granted to no Login, no Role (the tables cannot be accessed and changed directly, you must go through a stored proc, which enforces transactional integrity)
f the tables are INSERT/UPDATE/DELETEd via stored procs only
g the stored procs have EXECUTE granted to specific Roles
h a number of Roles have been set up in the db to EXECUTE procs
Now life is easy:
- When you add a new user, you simply add a Login, and add them as an User to each db, read-only access for all dbs/tables. If (c) you can stop right there.
- Where (d) is in place, Grant the Login whatever (read-only) Roles are required for their job function, which are authorised by their manager.
- For update access, Grant the Login whatever (execute proc) Roles are required for their job function which are authorised by their manager.
If you do not have the above minimum security in place, then life is hard labour in an American prison. There's no way for us to know what needs to be done, we need to know what is in place first. Depending on what you have at your site, it could be anything between the above and the below. Here's the minimum:
1 Ensure the dbs have a 'guest' user
2 Grant SELECT/REFERENCE to 'public' (public is the group you belong to when you do not belong to a group)
3 Ensure INSERT/UPDATE/DELETE is not Granted to 'public'
4 Add the new user as a Login
5 For each db, add the Login as an User.