Originally written in French last year, now revised for you in my native language below:
You've probably all noticed a change since SQL Server 2000 with respect to objects: formerly DB.ObjectOwner.Object , now DB.Schema.Object since 2005. With Schemas, we can benefit from the grouping of the objects to make sense of our often complicated relational database management systems. For sure, a user can still create an object within the database as UserName.Object but that is a little messy to clean up when that person leaves the company. Sp_rename can be used to fix those pesky messes however, if you have to deal with them.
Examples:
Grant select, insert, update, execute on Schema::SchemaName to [JoBlow]
-- this is assuming that you have used a specific user JoBlow generated and SchemaName
-- if you are using the built-in roles, then see below
CREATE USER [Domain\UserName] FOR LOGIN [Domain\UserName] WITH DEFAULT_SCHEMA= [db_datareader]
-- if you want a user to have read access, make their default schema db_datareader and not DBO
-- if you give them DBO, then they have database owner rights (!) and can control all roles and schemas
USE [DatabaseName]
-- if you have to elevate their rights, then use the db_datawriter role
GO
EXEC sp_droprolemember N'db_datareader',
N'Domain\UserName'
GO
USE [DatabaseName]
GO
EXEC sp_addrolemember N'db_datawriter',
N'Domain\UserName'
GO
When we create an object, we can simply associate it within a schema, which in turn makes managing the security on that group of objects easier. The other advantages are that we can place a schema within the database by the name of the application that uses it, perhaps if multiple applications depend on a single database, making it easy to understand which objects are used by what/whom/how.
As you can see in the above example, it's easier to manage the security on the group of objects too, since you would set the permissions on schema level and not for each individual object - noticeably tedious beforehand. Giving DBO (database owner schema which still exists) access to users is not the best solution to handle security, if you wish to maintain the principle of least privilege. A preferred workaround for elevated privileges access would be by means of EXECUTE AS instead (great current article on that here).
For users of RedGate's SQL Compare 8 (divided up into Schema or Data Compare previously), the use of schemas is particularily useful from the point of view of a DBA trying to keep his objects in synch between the various stages of development (e.g. copy of production into Test environment, followed by a merge with development DB, to approve changes). If the grouping of the objects worked on is based on schemas, then the turnaround time in development will be faster (assuming the use of a catch-all database for several applications), since there is the option to compare by schema only using the SQL Compare tool. Similarly, O'Shea gives a full-on real-world use of Schema Comparison and Snapshots here.
References:
· http://www.sqljunkies.com/WebLog/outerjoin/archive/2006/02/24/17635.aspx
· http://www.sqlteam.com/article/understanding-the-difference-between-owners-and-schemas-in-sql-server
