Just think of the schema as a container to organize objects and simplify granting permissions as opposed to the earlier notion of owner. Most importantly, the schema organization must be considered early in the design process to avoid problems with code later on. Finally, by granting Execute permission in a schema to an account or database role, we now have a way to make sure that users can always execute new stored procedures. The use of schemas is exceptionally beneficial when you have security concerns.
By default, objects (tables, stored procedures, etc) are set up with the dbo owner/schema.
If you have multiple applications that access the database, you might not want to give the Logistics department access to Human Resources records. So you put all of your Human Resources tables into an hr schema and only allow access to it for users in the hr role.
Six months down the road, Logistics now needs to know internal expense accounts so they can send all of these palettes of blue pens to the correct location people. You can then create a stored procedure that executes as a user that has permission to view the hr schema as well as the logistics schema. The Logistics users never need to know what’s going on in HR and yet they still get their data.
In SQL Server 2005 or later versions, a schema can be owned by a database role
(by user in prev versions), and you can grant this role to a specify user. If you want to delete this user, you would get any problems. The schema, role and user make a hierarchy structure. The schema is the container of objects, the role is the container of schemas and the user is the container of roles, which makes it, is easy to manage database objects
