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.

Related Posts

Data Discovery And Classification In SQL Server

Gilad Mittelman explains how the SQL Information Protection (aka Data Discovery and Classification) process works in SQL Server and Azure SQL Database: SQL Information Protection (SQL IP) introduces a set of advanced services and new SQL capabilities, forming a new information protection paradigm in SQL aimed at protecting the data, not just the database: Discovery […]

Read More

Using Group-Managed Service Accounts With SQL Server

Wayne Sheffield has a post on using gMSA with SQL Server: A gMSA is a sMSA [standalone managed service account] that can be used across multiple devices, and where the Active Directory (AD) controls the password. PowerShell is used to configure a gMSA on the AD. The specific computers that it is allowed to be […]

Read More

Categories

January 2018
MTWTFSS
« Dec Feb »
1234567
891011121314
15161718192021
22232425262728
293031