Intoductino

Problem

What is it?

The main purpose of this process is to list, provision, and maintain a central repository of all SQL Server permissions.

Only what is in the table gets provisioned.

How to use it? How to provision a new access?

  1. Create a new one or use an existing AD group
  2. Run DBA.security.usp_add_to_access_list

Example:

EXEC DBA.security.usp_add_to_access_list @command_name = 'ALTER ROLE', @database_name = 'DBA', @permission_name = 'ADD MEMBER', @role_name = 'db_datareader', @login_name = 'domain_name\group_name'

Architecture

Essential Components

Job

_Maintenance_061_AssignPermissions_Bouncer – triggers usp_Bouncer procedure

Schedule: Runs every 1 minute

Database

DBA

Schema

Security – groups all the security-related database objects

Tables (the brain)

AccessList – stores all the permissions to provision

AccessLog – logs all executions of security.usp_Bouncer driven by AccessList table

Views

Grouping permissions

  • vw_custom_database_roles_permissions – custom database role permissions
  • vw_custom_database_roles – custom database roles
  • vw_database_object_permissions – permission on a database objects
  • vw_server_and_database_roles – server and database fixed roles
  • vw_server_permissions – server level permissions

Stores procedures (the muscle)

Add access

usp_add_to_access_list – feeds new permissions to AccessList table

Provision access

  • usp_assign_server_role_to_login – add login to existing fixed server role
  • usp_assign_server_permission_to_object – grant object permission on a server level
  • usp_create_custom_database_role – custom database role permissions
  • vw_database_object_permissions – grant object permission on a database level
  • usp_assign_database_role_to_login- server and database fixed roles
  • usp_assign_database_permission_to_role- server level permission
  • usp_Bouncer – iterate through AccessList table, using views to decide which procedure is needed to grant permission

Dropping access

usp_DropLoginandUsers – currently not in-use

usp_IndentifyForeignLoginandUsers – currently not in-use

Process diagram

Reporting

Essential Components

Job

_Maintenance_31_AssignPermissions_Bouncer_Errors_01

Schedule: Everyday every 1 minute

Database

DBA