Using Schemas For Database Management

Jana Sattainathan explains the benefits of using schemas to segment out functional sections of a database:

My recommendation on how to manage permissions goes like this:

1. Create Database with appropriate Schemas – like HR/Finance (or) Staging/ETL etc
2. Create objects like tables and views inside the appropriate Schemas
3. Create database roles such as db_finance_admin_role, db_developer_role, db_ddl_deployer_role etc
4. Grant permissions at the Schema level to database roles as shown in the example above
5. Create AD groups (instead of individual logins) like Finance_DB_Admins, IT_Developers etc
6. Grant database role membership to AD groups instead of individual logins – EXEC sp_addrolemember N’db_developer_role’, N’IT_Developers’

Doing it this way allows you to separate the concerns. For example the db_developer_role can be granted more or less permissions and all the groups granted that role will automatically get that. Also, you are free to use the AD groups across instances in multiple databases with different permissions.

Click through for more details, including how to get to separate schemas from an all-dbo database.