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

Azure Databricks Security

Tristan Robinson looks at what’s currently available in terms of security on Azure Databricks: You’ll notice that as part of this I’m retrieving the secrets/GUIDS I need for the connection from somewhere else – namely the Databricks-backed secrets store. This avoids exposing those secrets in plain text in your notebook – again this would not […]

Read More

Miminal Rights For Bulk Inserts

Timothy Smith takes us through least privilege while allowing bulk insert operations: While this file path serves as a useful location for us to load flat files, we should consider that the user account that is executing the underlying insert statement must be able to read (and possibly write to) that file location. The writing […]

Read More

Categories

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