SQL Best Practice: Use Windows groups instead of indiv windows logins question

Question

SQL Best Practice: Use Windows groups instead of indiv windows logins question

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

To take a little further you can create a base role and and exteneded role which is also a member of the base role. Example where this would be seen.

HR application, all HR employees can see employee information in the employee table so you create a role with select rights on a set of views (preferred is not to give direct table access) or exec rights on a set of stored procedures.

Now you have a group of people in HR who deal only with payroll, so you create a payroll role with rights on views and stored procedures related to payroll work, but they also can view the standard list of items such as employee info so you add to the base hr role (when I say base these are the functions anyone in hr can do).

Then you have a group who deal with benifits, they get rights on views and procs related to their function, but so payroll doesn't have to manage deductions these folks are added to payroll role to prevent payroll from having to do work everytime bennifits for an employee change, and as the payroll role is part of hr base role you don't have to add bennifits role directly to it as it will inherit thru the chain those from payrole.

Now you have a management group which handles terminating employees in the system and their closeout of payments or receviables with regards to the employee, so manage role, but they will do some payrole and bennifits functions so add to bennifits and they get all they will need.

Then along comes a network group which handles access to your servers and thru good intergration software the DB drives access, so you create a access controls group which as they don't need payrole or bennifits you add only to HR base role. And since management group needs to perform some tasks with access controls you add to this role as no chain allows them to use this groups functions.

You can see where this complicated by more and more roles but if you develope a good logical design and avoid exceptions then the above may handle you HR department nicely and all you have to do is add the people to the right group or groups (could be a case where a payrole person may have need to handle access control stuff so you put them in both payrole windows group and access control windows group gives them exactly what they need).