In this example I'm using the AdventureWorks database in MS SQL Server 2005
I was wondering what other people thought about Schemas vs. Roles when it comes to security access. If you have an employee who just started working for Human Resources and needed access to all the database objects in HR, do you add the new hire to an existing role or do you assign that person permissions to the HumanResources schema? Either way the user can be configured to work in HR by using a Schema or Role. What are the pros and cons to either one and what are the reasons to assign a user to a Schema instead of adding them to a Role?
What I figure there are always going to be exceptions that just don't fit into what may already be available that you could use a mix of both.