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

Limiting Azure Administrator Data Access

Melissa Coates gives us a look at one aspect of Azure security: Recently a customer expressed concern that an owner of an Azure resource group automatically gains access to the data within the services contained in the resource group. In this case, the customer was specifically referring to data in Azure Data Lake Storage Gen […]

Read More

SQL On Linux AD: Group Membership Issues

Dylan Gray and Tejas Shah continue their series on troubleshooting issues when connecting to SQL Server on Linux via an Active Directory account: Imagine a scenario where after a successful AD login and running a couple queries, some users may see the error “Could not obtain information about Windows NT group/user ‘CONTOSO\user’.” This is due to a […]

Read More

Categories

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