Security Script Organization

• 
• Common Variables table sets up some variables used within tblOpIRolesGRANT and FIXED and should be set up first.
• The Scripts are enumerated purposefully, 1-through-5. In Setup, Scripts should be executed in the order of enumeration. Security_1 Scripts (GRANT and FIXED) create Role predicates for Server Logins set up in Security_2 Script. Security_2 ServerLogins are predicates for Security_5 Script that creates DBUsers.
• Security_3 Script creates SCHEMAs, and Security_4 Scripts (GRANT and FIXED) create Role predicates for DBUsers set up in Security_5 Script.
• On tear-down of any parameter (e.g. deletion of SvrLogin or DBUser or Role, Scripts should be executed in REVERSE: 5 before 4 etc. The Scripts will delete dependent table entries, but manual tear-down of known dependent entries will help prevent table clutter of entries with unknown or obsolete purpose (which could potentially contribute to security or integrity issues in undetermined fashion at a later date).

Permission Variables Setup

Set Common Permission Variables

• tblOpIOnObj
  

OpISQLServerSecurity Operation

Each operation of OpISqlServerSecurity involves executing a SQL Script from SSMS, which uses data from one or more Tables within OpISQLServerSecurity\ToolSource.xlsm. Note that the Tables may be pushed up into the SQL Server database engine, covered below in section Push Tables.

Before running a Script, Tables involved by that Script, shown in descriptions below, should be updated as necessary to add intended function: Server Logins, Roles, Database Users, along with configuration information for each. Then, open SQL Server Management Studio (SSMS) and from the SSMS File menu open the Script named in the descriptions in the following paragraphs to perform the operation. For example, to CREATE/ALTER/DROP a Server role, open Script SECURITY_1-CREATE-Svr-GRANT-Roles.sql in SSMS. Select the SSMS tab holding the Script for the intended operation, and click the SSMS menu bar Execute button, or right-click the Script and select Execute.

The Script will then perform actions specified in every line of the affected Tables that is in-scope of the Script operation, which is described in sections below on this page. That action includes JOINing tables together or with needed system tables, picking up field values from those Tables and creating one or more lines of SQL responsive to the field values among JOINed tables. The resulting SQL is then executed to effect the intent of the in-scope Table lines - for example, creating a new Server Role, configuring it with permissions, and adding it as a member of other specified roles. (FYI, as Server Logins are subsequently created, they can then be added as members of the created Roles to inherit their permissions and resultant capabilities.)

Tools for .sql Scripts: We're using the term SSMS, but in practice there are several tools that can be used. First, some explanation of addressing to connect these tools to the database against which a Script will execute. Then, we'll identify several tools and outline their capabilities for use with SQL scripts.

• Connection and Addressing Open the .sql script file to run, then right-click and choose the connection dialog. These are formats for database address:
  • ServerComputerName\SQLServerInstanceName
    Example: MyServerName\SQLEXPRESS
  • ServerIPAddress:SQLServerInstancePort
    Example: 192.168.1.87:1433
    Showing a local computer, but remote IP is valid too. Port specification is optional; if not specified, connection will choose default port, typically 1433, set up in Install Instructions.
  • Server computer Firewall must be set up as specified in Install Instructions.
• Tools to Execute, Edit, or Debug .sql scripts.
  • Microsoft SSMS (SQL Server Management Studio)
    Edit or Execute .sql script.
  • Microsoft VSCode - Visual Studio Code
    Edit or Execute .sql script.
  • Microsoft Azure Data Studio
    Edit or Execute .sql script.
  • Microsoft Visual Studio
    Edit or Execute .sql script.
    Debug .sql script.
    See Debugging SQL for installation, configuration, and operation of debug.

Each Script will only act on Table lines within its scope. The scope is typically defined by the leftmost fields of each Table, such as rolScope (Svr or DB), rolFixedGrant (Grant or Fixed), and rolActiveNow (Yes, No, DryRun). For ActiveNow fields, a "Yes" value indicates intent to execute that line; "No" indicates that line should be skipped. "Dry Run" indicates that SQL should be fully constructed but should NOT be Executed.

For all Tables, the term "ActiveNow" for each line indicates whether that line will be executed when SSMS Scripts are invoked. The term is NOT an indication of existence of the entity described on the line, rather it is an indication that action will be taken by the Script for that line when it is in scope for the Script. Scripts generally look at Scope and Verb and will use other fields as well as ActiveNow to determine action.

Each line of created SQL is echoed to the SSMS console as it is executed, and includes the Table line number involved for reference back to the source Table. Upon execution, if an error is reported by the Database Engine, the error information is echoed to the SSMS console. If the ActiveNow field indicates "Dry Run", the constructed SQL line is echoed to the SSMS console, but the line is not executed so no error will be detected by the Database Engine or echoed to the SSMS console. Nevertheless, the SQL can be inspected in the SSMS console for correct form and configuration.

Each tab in OpISQLServerSecurity\ToolSource.xlsm that contains a Table associated with a Script, notes the Script(s) that use the Table, and adds operational notes for the Table and Scripts. Some operational notes and references are also noted at the top of each SQL Script source, visible when open in SSMS.

You may find the SQL in the SCRIPTS to be interesting. It includes numerous constructs:

• Use of local @variables, including setting value from QUERY SELECT items.
• Use of SQL Cursor to scan through source Tables, including layering of cursors within cursors.
• Use of Temporary Tables for intermediate data extraction.
• QUERYs with JOINs into SQL Server system Tables.
• COUNTing records returned from a QUERY, and determination that the count is more than zero.
• Construction of dynamic SQL QUERYs. CONCAT function is used rather than + operator, in order to correctly handle values with NULL value.
• Executing dynamic SQL statements using sp_executesql system stored procedure, including operations on System Tables, and reporting via SSMS Console.
• TRY-CATCH structure to catch and handle errors as SQL statements are executed, including error reporting.

CREATE - ALTER - GRANT - DROP Server-Role

CREATE Server Roles

• tblOpIRolesCreate
  

Server GRANT-Role Permissions

• tblOpIRolesGRANT
  

• Using Script: SECURITY_1-CREATE-Svr-GRANT-Roles.sql
• This Script will act on the role named in each line by field rolRoleName where rolScope = Svr, rolFixedGrant = Grant, rolActiveNow = Yes or DryRun, rolOpIVerb is not NULL.
• This Script will act on Permissions for each such rolRoleName where grScope = Svr, grVerb is not NULL and rolRoleName = grRole.
• This Script will act on Memberships by such rolRoleName into Role rolMemberOfRole where rolMemberOfVerb is not NULL.
• Descriptions of Script function include syntax definitions showing how Table columns are used in the operation. The prefix lower-case characters in each name indicate the Table from which a column is sourced: rol for role definition table tblOpIRolesCreate, gr for Grant Permission definition table tblOpIRolesGRANT, fix for Fixed-Role definition table tblOpIRolesFIXED, lin for Login definition tblOpILogins, sch for tblOpISchemaCreate; usr for tblOpIUsers; and on for variable-definition table tblOpIOnObj.

• rolOpIVerb = DROP

  For each role rolRoleName described by a line in tblOpIRolesCreate, OpISQLServerSecurity, Script first DROPs all memberships by that role, in other roles.

  Next, Script DROPs all memberships within the specified role.

  Finally, Script DROPs the specified role itself.

  -- DROP rolRolename Membership in otherRoles
  ALTER roleType -- from system lookup otherRole = SERVER
       ROLE otherRoleName -- from system lookup, thisRole is member of otherRole
       DROP MEMBER rolRoleName; -- drops membership of thisRole in otherRole
      -- Loops through all memberships in other Roles.
  
  -- DROP members of rolRolName
  ALTER roleType -- from system lookup thisRole = SERVER
      ROLE rolRoleName
      DROP MEMBER memberName; -- drops membership of memberName in thisRole
      -- Loops through all memberships within this Role.
  
  -- DROP Server Role
      DROP roleDesc ROLE rolRoleName; -- roleDesc from system lookup of thisRole
  

• rolOpIVerb = ADD/UPDATE

  Script First CREATEs a Server Role, if it does not already exist. If a role by that name already exists, the existing role will be updated.

  CREATE SERVER ROLE rolRoleName AUTHORIZATION sysadmin;
  

  For each role named by field rolRoleName described by a line in OpISQLServerSecurity tblOpIRolesCreate, Script loops through permissions in tblOpIRolesGRANT, and executes the grVerb in each of those SERVER ROLE permissions to GRANT, DENY, or REVOKE each permission for that Role (where grRole = rolRoleName).

  Then Script executes rolMemberOfVerb to ADD or DROP membership of that role rolRoleName in the role specified by rolMemberOfRole.

  Membership of rolRoleName in additional roles may be specified by adding a line for each ADD or DROP needed.

  Note use of Table column-name variables using lower-case prefixes that indicate the source table for the variable. Prefixes are enumerated above.

  -- Syntax definition
  GRANT <permission> [ ,...n ]
       [ ON [ <class_type> :: ] securable ]
       TO principal [ ,...n ]
            [ WITH GRANT OPTION ]
       [ AS <database_principal>
      ]
      [;]
  
  -- Implemented by Script using field specifications in tblOpIRolesCreate and tblOpIRolesGRANT
  grVerb grPermission
       [ grOnObject onObj [ onObjectName | rolDatabase /* if onObjectName=onDatabase */; ] ]
       TO grRole
            [ WITH GRANT OPTION -- if grVerb = GRANT and grGrantOpt = Yes
                 | CASCADE -- if grVerb IN(REVOKE | DENY) and grGrantOpt = Yes
            ] -- if grGrantOpt=Yes
       [ AS onObjectName | sysadmin /* if onObjectName=onDatabase */
       ] -- if grAS not NULL
  [ ; ]
  

  Finally, Script executes rolMemberOfVerb to add or drop membership of role rolRoleName in upstream role rolMemberOfRole.

  ALTER SERVER ROLE rolMemberOfRole rolMemberOfVerb MEMBER rolRoleName;
  

  Membership by a created role in additional upstream roles can be added or dropped by specifying rolMemberOfVerb = ADD or DROP in additional records/lines in tblOpIRolesCreate.

• rolOpIVerb = ALTER

  No operation is defined for rolOpIVerb = ALTER. rolOpIVerb ADD/UPDATE uses CREATE and ALTER Role management keywords internally within its functionality, but much other functionality is embedded with this opcode so these SQL keywords are not offered directly by the OpISQLServerSecurity tool.

• References

  CREATE Server Role

  ALTER Server Role

Server FIXED-Role Memberships

• tblOpIRolesFIXED
  

• Using Script: SECURITY_1-CREATE-Svr-FIXED-Roles.sql
• This Script will act on the role named by field rolRoleName in each line where rolScope = Svr, rolFixedGrant = Fixed, rolActiveNow = Yes or DryRun, rolOpIVerb is not NULL.
• This Script will act on Fixed Role memberships for rolRoleName where fixScope = Svr (adding or dropping rolRoleName as a member of Server-level Fixed Role fixFixedRole to inherit or remove capabilities of fixFixedRole), and where fixVerb is not NULL and rolRoleName = fixRole.
• This Script will act on Memberships in rolRoleName into Role rolMemberOfRole to inherit or remove rolMemberOfRole capabilities.
• Descriptions of Script function include syntax definitions showing how Table columns are used in the operation. The prefix lower-case characters in each name indicate the Table from which a column is sourced: rol for role definition table tblOpIRolesCreate, gr for Grant Permission definition table tblOpIRolesGRANT, fix for Fixed-Role definition table tblOpIRolesFIXED, lin for Login definition tblOpILogins, sch for tblOpISchemaCreate; usr for tblOpIUsers; and on for variable-definition table tblOpIOnObj.

• rolOpIVerb = DROP

  For each role rolRoleName described by a line in tblOpIRolesCreate, OpISQLServerSecurity Script first DROPs all memberships by that role, in other roles.

  Next, Script DROPs all memberships within the rolRoleName.

  Finally, Script DROPs role rolRoleName itself.

  -- DROP rolRoleName Membership in otherRoles
  ALTER roleType -- from system lookup otherRole = SERVER
       ROLE otherRoleName -- from system lookup, thisRole is member of otherRole
       DROP MEMBER rolRoleName; -- drops membership of thisRole in otherRole
      -- Loops through all memberships in other Roles.
  
  -- DROP members of rolRoleName
  ALTER roleType -- from system lookup thisRole = SERVER
      ROLE rolRoleName
      DROP MEMBER memberName; -- drops membership of memberName in thisRole
      -- Loops through all memberships within this Role.
  
  -- DROP Server Role
      DROP roleDesc ROLE rolRoleName; -- roleDesc from system lookup of thisRole = SERVER
  

• rolOpIVerb = ADD/UPDATE

  Script First CREATEs a Server Role by name rolRoleName, if it does not already exist. If a role by that name already exists, the existing role will be updated.

  CREATE SERVER ROLE rolRoleName AUTHORIZATION sysadmin;
  

  For each role named by field rolRoleName by a line in tblOpIRolesCreate, OpISQLServerSecurity, Script loops through Server-level Fixed Roles in tblOpIRolesFIXED, and executes the fixVerb (ADD or DROP) in each of those SERVER ROLE permissions to ADD or DROP membership of fixRole (where fixRole = rolRoleName) in role fixFixedRole to inherit capabilities of fixFixedRole into fixRole (=rolRoleName), or drop those capabilities from fixRole (=roleRoleName).

  Note use of Table column-name variables using lower-case prefixes that indicate the source table for the variable. Prefixes are enumerated above.

  -- Implemented by Script using field specifications in tblOpIRolesCreate and tblOpIRolesFIXED
  ALTER SERVER ROLE fixFixedRole fixVerb MEMBER rolRoleName;
  

  Finally, Script executes rolMemberOfVerb opcode to add or drop membership of role rolRoleName in upstream role rolMemberOfRole.

  ALTER SERVER ROLE rolMemberOfRole rolMemberOfVerb MEMBER rolRoleName;
  

  Membership by a created role in additional upstream roles can be added or dropped by specifying rolMemberOfVerb = ADD or DROP in additional records/lines in tblOpIRolesCreate.

• rolOpIVerb = ALTER

  No operation is defined for rolOpIVerb = ALTER. rolOpIVerb ADD/UPDATE uses CREATE and ALTER Role management keywords internally within its functionality, but much other functionality is embedded with this opcode so these SQL keywords are not offered directly by the OpISQLServerSecurity tool.

• References

  Database object identifiers

  Rules for T-SQL Identifiers

  Alter Server Role

CREATE - ALTER - DROP Server Login

The default table entries will set up default Logins, with default Passwords. Use them for setup and test.

Logins, and consequently Database Users described farther below, in the following descriptions and in the downloadable OpISQLServerTool, are named in linLoginName and usrUserName for the Role that they play: OpISuper, OpIManager, OpIDataOwner, OpIClient. This is to show clearly the relationships among Logins, Database Users, Server Roles and Database User Roles. In production implementation, those Principals (Logins and Database Users) would be named for individuals, organizational positions, or the like; not by these default names.

Before you Go-Live, change these Principal names and passwords, or set up different Server Logins for production use, and DISABLE these default logins. Every user of OpISQLServerSecurity will gain the same default user names and passwords on install or over time, and could possibly use them to access your database if you have left them intact - so disable the defaults before that happens.

CREATE Server Logins

• tblOpILogins
  

• Using Script: SECURITY_2-CREATE-Svr-Logins.sql
• This Script will act on the SQL Server Login named in each line by field linLoginName where linScope = Svr, linActiveNow = Yes or DryRun, linOpIVerb is not NULL.
• Descriptions of Script function include syntax definitions showing how Table columns are used in the operation. The prefix lower-case characters in each name indicate the Table from which a column is sourced: rol for role definition table tblOpIRolesCreate, gr for Grant Permission definition table tblOpIRolesGRANT, fix for Fixed-Role definition table tblOpIRolesFIXED, lin for Login definition tblOpILogins, sch for tblOpISchemaCreate; usr for tblOpIUsers; and on for variable-definition table tblOpIOnObj.

• linOpIVerb = DROP

  this Script will first drop all memberships of Login linLoginName in all Server Roles, and then DROP Server Login linLoginName.

  DROP Server Login

  -- DROP linLoginName Membership in foundRoles
  ALTER roleType -- from system lookup foundRole = SERVER
       ROLE foundRoleName -- from system lookup, linLoginName is member of foundRole
       DROP MEMBER linLoginName; -- drops membership of linLoginName from foundRole
      -- Loops through all memberships in found Roles.
  
  -- DROP Role membership
      DROP LOGIN linLoginName;
  

• linRoleVerb = ADD/UPDATE

  If linLoginName does not exist it will be created. If Login linLoginName already exists when the Script is run with linRoleVerb = ADD/UPDATE, then Login linLoginName will be ALTERED.

  In each line, if linOpIVerb = ADD/UPDATE, this Script will CREATE or ALTER a Login to SQL Server using protocol and settings specified by field linOpILoginWindows for Windows, SQL Server, Certificate, Asymmetric Key, or Server Credential.

  Fields within tblOpILogins that apply to each Login protocol are specified in rows just above the Field Name header of that table. Use of the fields in SQL used to effectuate the action is also illustrated in images below in this section. A Field not used for a particular linLoginName will be ignored and may be left blank.

  For each line in tblOpILogins, this Script, linRoleVerb will additionally ADD, DROP or ignore Login linLoginName membership in Server Role linRoleName. Membership of existing linLoginName in multiple Server linRoleNames will be ADDED or DROPPED as specified in each of multiple lines in tblOpILogins.

  Finally, this Script will GRANT, REVOKE, or DENY individual Permissions outside of Role memberships, to individual Server Logins. For this, add a row for each such Permission in tblOpIRolesGRANT for each Server Login, specifying Permission variables. Specify grRole as the Server Login name linLoginName, and specify grRoleClass as "SvrLogin". This Script will execute each grVerb to GRANT, REVOKE, or DENY each specified Permission to each such specified Server Login.

  CREATE Server Login

  

  ALTER Server Login

  

  tblOpILoginsFieldValues
  

• References

  Login CREATE Reference

CREATE Database

This SSMS Script created from SQLServerRemote\ToolManagement.xlsm and residing in script directory as "CREATE_Server1DatabaseName1" creates a Program/Project database.

You must set variable @dbname, and the instance "SQLServerRemoteDB" before running the Script.

/* SQL Server CREATE Database */

/* #VBTEXTJOIN: EXPORTWITH dbo; */

DECLARE @dbname nvarchar(128)
SET @dbname = N'SQLServerRemoteDB'

IF (NOT EXISTS (SELECT name FROM master.dbo.sysdatabases
  WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
    BEGIN
      CREATE DATABASE SQLServerRemoteDB

    END;
GO

USE SQLServerRemoteDB;
GO

IF EXISTS(SELECT is_trustworthy_on, name
  FROM sys.databases
  WHERE name = 'SQLServerRemoteDB' AND is_trustworthy_on=0)
ALTER DATABASE SQLServerRemoteDB SET TRUSTWORTHY ON;

ALTER Database

DROP Database

DROP DATABASE [ IF EXISTS ] { database_name | database_snapshot_name } [ ,...n ] [;]

Database CONNECTION

To run Database SQL Scripts 3, 4, 5 you must first Connect the Script to the Database to which Script is directed.

If you're already connected to the Server, in SSMS open the <scriptname>.sql file. Then click in the open script tab and select the database that you want to execute on from the QUERY toolbar dropdown.

If un-connected, or if you need to re-connect with a Login with permissions to execute the script, you can select the database to execute the open script on, from within the SSMS connection sequence. Open the <scriptname>.sql file, an right-click in it (red arrow starting in the middle of the figure below). Then follow the path shown to select "Connect...", "Options>>", "<Browse server...>", and select the database to exectue the script on.

You need to connect like this to manage database Schemas, User and Fixed Roles, and Database Users.

CREATE - ALTER - DROP Database Schema

CREATE Database Schema

• tblOpISchemaCreate
  

• First: Connect to intended Database.
• Using Script: SECURITY_3-CREATE-DB-Schema.sql
• This Script will act on the Database Schema specified in each line by field schSchemaName where schScope = DB, schActiveNow = Yes or DryRun, schSchemaVerb is not NULL.
• A Schema contain Tables and Views, and Database User Permissions can be set relative to Domain containing an object. So Tables with sensitive information could be held within a domain to which only specified Database Users have access. Such objects would be invisible and un-usable to Users without permissions on those Domains. The Permissions can be specified in a Database User Role; a User inherits its Permissions if assigned membership in that Role.
• OpISQLServerSecurity puts Program data in tables in the dbo Domain, which is a SQL Server default Domain along with user and sys Domains. OpISQLServerSecurity default table values set up Domains, Logins and Database Users for Domains hr (Human Resources), fin (Finance), mgr (Management), and opi (OpI Security Tables). These domains could be used to contain Tables with project-related sensitive information.
• When creating a Schema, objects contained by it can be created it the same CREATE command. This is not supported by the PowerOpI CREATE implementation; objects must be created in separate SSMS SQL commands and can be transferred to the SCHEMA by the ALTER opcode of this Script.
• Descriptions of Script function include syntax definitions showing how Table columns are used in the operation. The prefix lower-case characters in each name indicate the Table from which a column is sourced: rol for role definition table tblOpIRolesCreate, gr for Grant Permission definition table tblOpIRolesGRANT, fix for Fixed-Role definition table tblOpIRolesFIXED, lin for Login definition tblOpILogins, sch for tblOpISchemaCreate; usr for tblOpIUsers; and on for variable-definition table tblOpIOnObj.

• schSchemaIVerb = DROP

  This Script DROPs an existing Schema.

  DROP Schema

  DROP SCHEMA [IF EXISTS] schSchemaName;
  

• schSchemaVerb = ADD/UPDATE

  If Database Schema schSchemaName does not exist it will be created.

  CREATE Database Schema

  CREATE SCHEMA schSchemaName AUTHORIZATION [schOwnerDomain\] schOwnerName;
      -- schOwnerName is typically db_securityadmin
  

• schSchemaVerb = ALTER

  ALTER Database Schema

  Schema objects, such as a Table (dbo.tblTableName) are created within a Schema, for which the creator has ALTER SCHEMA permission within the Database in which the Object is created. That could be within the intended Schema, or in another Schema. An Object is created in a Schema different than its target Schema, can be transfered into the Target Schema, again by a creator with ALTER SCHEMA permission. This is the function of the ALTER opcode within Script SECURITY_3-CREATE-DB-Schema.sql.

  ALTER SCHEMA schSchemaName 
      TRANSFER [schTransferEntityType::][schTransferFromSchema.]schObjectToTransfer;
  

• References

  SQL Server Security Overview

  Database Engine Permissions Poster (download PDF)

CREATE - ALTER - GRANT - DROP Database Role

CREATE Database Roles

• tblOpIRolesCreate
  

Database User GRANT-Role Permissions

• tblOpIRolesGRANT
  

• First: Connect to intended Database.
• Using Script: SECURITY_4-CREATE-DB-GRANT-Roles.sql
• This Script will act on the Database role named in each line by field rolRoleName where rolScope = DB, rolFixedGrant = Grant, rolActiveNow = Yes or DryRun, rolOpIVerb is not NULL.
• This Script will act on Database-Level Permissions for each such rolRoleName where grScope = DB, grVerb is not NULL and rolRoleName = grRole.
• This Script will act on Memberships by such rolRoleName into Database Role rolMemberOfRole where rolMemberOfVerb is not NULL.
• Descriptions of Script function include syntax definitions showing how Table columns are used in the operation. The prefix lower-case characters in each name indicate the Table from which a column is sourced: rol for role definition table tblOpIRolesCreate, gr for Grant Permission definition table tblOpIRolesGRANT, fix for Fixed-Role definition table tblOpIRolesFIXED, lin for Login definition tblOpILogins, sch for tblOpISchemaCreate; usr for tblOpIUsers; and on for variable-definition table tblOpIOnObj.

• rolOpIVerb = DROP

  For each Database role rolRoleName described by a line in tblOpIRolesCreate, OpISQLServerSecurity, Script first DROPs all memberships by that role, in other Database roles.

  Next, Script DROPs all memberships within the specified Database role.

  Finally, Script DROPs the specified Database role itself.

  -- DROP rolRolename Membership in otherRoles
  ALTER ROLE otherRoleName -- from system lookup, thisRole is member of otherRole
       DROP MEMBER rolRoleName; -- drops membership of thisRole in otherRole
      -- Loops through all memberships in other Roles.
  
  -- DROP members of rolRolName
  ALTER ROLE rolRoleName
      DROP MEMBER memberName; -- drops membership of memberName in thisRole
      -- Loops through all memberships within this Role.
  
  -- DROP Role
      DROP ROLE rolRoleName; -- roleDesc from system lookup of thisRole
  

• rolOpIVerb = ADD/UPDATE

  Script First CREATEs a Database-Level Role, if it does not already exist. If a Database role by that name already exists, the existing role will be updated.

  CREATE ROLE rolRoleName AUTHORIZATION db_owner;
  

  For each Database role named by field rolRoleName described by a line in OpISQLServerSecurity tblOpIRolesCreate, the SSMS Script loops through permissions in tblOpIRolesGRANT, and executes the grVerb in each of those Database Role permissions to GRANT, DENY, or REVOKE each permission for that Role (where grRole = rolRoleName).

  Then Script executes rolMemberOfVerb to ADD or DROP membership of that Database Role rolRoleName in the Database role specified by rolMemberOfRole.

  Membership of rolRoleName in additional Database roles may be specified by adding a line for each ADD or DROP needed.

  Note use of Table column-name variables using lower-case prefixes that indicate the source table for the variable. Prefixes are enumerated above.

  -- Syntax definition
  GRANT <permission> [ ,...n ]
       [ ON [ <class_type> :: ] securable ]
       TO principal [ ,...n ]
            [ WITH GRANT OPTION ]
       [ AS <database_principal>
      ]
      [;]
  
  -- Implemented by Script using field specifications in tblOpIRolesCreate and tblOpIRolesGRANT
  grVerb grPermission
       [ grOnObject onObj [ onObjectName | rolDatabase /* if onObjectName=onDatabase */; ] ]
       TO grRole
            [ WITH GRANT OPTION -- if grVerb = GRANT and grGrantOpt = YES
                 | CASCADE -- if grVerb IN(REVOKE | DENY) and grGrantOpt = YES
            ] -- if grGrantOpt=Yes
       [ AS onObjectName | db_securityadmin /* if onObjectName=onDatabase */
       ] -- if grAS not NULL
  [ ; ]
  

  Finally, Script executes rolMemberOfVerb to add or drop membership of Database role rolRoleName in upstream Database role rolMemberOfRole.

  ALTER ROLE rolMemberOfRole rolMemberOfVerb MEMBER rolRoleName;
  

  Membership by a created Database role in additional upstream Database roles can be added or dropped by specifying rolMemberOfVerb = ADD or DROP in additional records/lines in tblOpIRolesCreate.

• rolOpIVerb = ALTER

  No operation is defined for rolOpIVerb = ALTER. rolOpIVerb ADD/UPDATE uses CREATE and ALTER Role management keywords internally within its functionality, but much other functionality is embedded with this opcode so these SQL keywords are not offered directly by the OpISQLServerSecurity tool.

• References

  CREATE Database Role

  GRANT Database Role Permissions

  ALTER Database Role

Database FIXED-Role Memberships

• tblOpIRolesFIXED
  

• First: Connect to intended Database.
• Using Script: SECURITY_4-CREATE-DB-FIXED-Roles.sql
• This Script will act on the Database FIXED role named by field rolRoleName in each line where rolScope = DB, rolFixedGrant = Fixed, rolActiveNow = Yes or DryRun, rolOpIVerb is not NULL.
• This Script will act on Database Fixed Role memberships for rolRoleName where fixScope = DB (adding or dropping rolRoleName as a member of Database-level Fixed Role fixFixedRole to inherit or remove capabilities of fixFixedRole), and where fixVerb is not NULL and rolRoleName = fixRole.
• This Script will act on Memberships in rolRoleName into Database Role rolMemberOfRole to inherit or remove rolMemberOfRole capabilities.
• Descriptions of Script function include syntax definitions showing how Table columns are used in the operation. The prefix lower-case characters in each name indicate the Table from which a column is sourced: rol for role definition table tblOpIRolesCreate, gr for Grant Permission definition table tblOpIRolesGRANT, fix for Fixed-Role definition table tblOpIRolesFIXED, lin for Login definition tblOpILogins, sch for tblOpISchemaCreate; usr for tblOpIUsers; and on for variable-definition table tblOpIOnObj.

• rolOpIVerb = DROP

  For each Database Fixed role rolRoleName described by a line in tblOpIRolesCreate, OpISQLServerSecurity Script first DROPs all memberships by that role, in other roles.

  Next, Script DROPs all memberships within the rolRoleName.

  Finally, Script DROPs Database Fixed role rolRoleName itself.

  -- DROP rolRoleName Membership in otherRoles
  ALTER ROLE otherRoleName -- from system lookup, thisRole is member of otherRole
       DROP MEMBER rolRoleName; -- drops membership of thisRole in otherRole
      -- Loops through all memberships in other Roles.
  
  -- DROP members of rolRoleName
  ALTER ROLE rolRoleName
      DROP MEMBER memberName; -- drops membership of memberName in thisRole
      -- Loops through all memberships within this Role.
  
  -- DROP Role
      DROP ROLE rolRoleName;
  

• rolOpIVerb = ADD/UPDATE

  Script First CREATEs a Database Fixed Role by name rolRoleName, if it does not already exist. If a role by that name already exists, the existing role will be updated.

  CREATE ROLE rolRoleName AUTHORIZATION db_owner;
  

  For each Database Fixed role named by field rolRoleName by a line in tblOpIRolesCreate, OpISQLServerSecurity, Script loops through Database-level Fixed Roles in tblOpIRolesFIXED, and executes the fixVerb (ADD or DROP) in each of those Database FIXED Role permissions to ADD or DROP membership of fixRole (where fixRole = rolRoleName) in role fixFixedRole to inherit capabilities of fixFixedRole into fixRole (=rolRoleName), or drop those capabilities from fixRole (=roleRoleName).

  Note use of Table column-name variables using lower-case prefixes that indicate the source table for the variable. Prefixes are enumerated above.

  -- Implemented by Script using field specifications in tblOpIRolesCreate and tblOpIRolesFIXED
  ALTER ROLE fixFixedRole fixVerb MEMBER rolRoleName;
  

  Finally, Script executes rolMemberOfVerb opcode to add or drop membership of Database Fixed role rolRoleName in upstream Database Fixed role rolMemberOfRole.

  ALTER ROLE rolMemberOfRole rolMemberOfVerb MEMBER rolRoleName;
  

  Membership by a created Database Fixed role in additional upstream Database Fixed roles can be added or dropped by specifying rolMemberOfVerb = ADD or DROP in additional records/lines in tblOpIRolesCreate.

• rolOpIVerb = ALTER

  No operation is defined for rolOpIVerb = ALTER. rolOpIVerb ADD/UPDATE uses CREATE and ALTER Role management keywords internally within its functionality, but much other functionality is embedded with this opcode so these SQL keywords are not offered directly by the OpISQLServerSecurity tool.

• References

  Database Roles

  Database object identifiers

  Rules for T-SQL Identifiers

CREATE - ALTER - DROP Database User

The default table entries will set up default Database Users, associated with default Server Logins. Use them for setup and test.

Database Users described in the following descriptions and in the downloadable OpISQLServerTool, are named in usrUserName for the Role that they play: OpISuper, OpIManager, OpIDataOwner, OpIClient. Database User names match Server Login names. This is to show clearly the relationships among Logins, Database Users, Server Roles and Database User Roles. In production implementation, those Principals (Server Logins and Database Users) would be named for individuals, organizational positions, or the like; not by these default names.

Before you Go-Live, change these Principal names and passwords, or set up different Server Logins and Database Users for production use, and DISABLE these default Database Users. Every user of OpISQLServerSecurity will gain the same default user names and passwords on install or over time, and could possibly use them to access your database if you have left them intact - so disable the defaults before that happens.

CREATE Database Users

• tblOpIUsers
  

• First: Connect to intended Database.
• Using Script: SECURITY_5-CREATE-DB-Users.sql
• This Script will act on the Database User specified in each line by field usrUserName where usrScope = DB, usrActiveNow = Yes or DryRun, usrOpIVerb is not NULL.
• Descriptions of Script function include syntax definitions showing how Table columns are used in the operation. The prefix lower-case characters in each name indicate the Table from which a column is sourced: rol for role definition table tblOpIRolesCreate, gr for Grant Permission definition table tblOpIRolesGRANT, fix for Fixed-Role definition table tblOpIRolesFIXED, lin for Login definition tblOpILogins, sch for tblOpISchemaCreate; usr for tblOpIUsers; and on for variable-definition table tblOpIOnObj.

• usrOpIVerb = DROP

  this Script will first drop all memberships of Database User usrUserName in all Database User Roles, and then DROP Database User usrUserName itself.

  DROP User from Database

  -- DROP usrUserName Membership in foundRoles
  ALTER ROLE foundRoleName -- from system lookup, usrUserName is member of foundRole
       DROP MEMBER usrUserName; -- drops membership of usrUserName from foundRole
      -- Loops through all memberships in found Roles.
  
  -- DROP Role membership
      DROP USER IF EXISTS usrUserName;
  

• usrRoleVerb = ADD/UPDATE

  If Database User usrUserName does not exist it will be created. If Database User usrUserName already exists when the Script is run with usrRoleVerb = ADD/UPDATE, then Database User usrUserName will be ALTERED.

  By nature of the ADD/UPDATE verb, the Database User usrUserName is associated with database usrDatabase, and optionally ADDED or DROPPED as specified by usrRoleVerb, as a member of Database User Role usrRoleName. Additional lines may be used to ADD/DROP the Database User usrUserName with additional Database Roles with database usrDatabase.

  In each line, fields usrIdentifierText and usrIdentifierToken accept free-form text to aid identification of the person associated with the Database User name usrUserName. That could include the person's full name, employee ID number, or other identifying information. Content of these fields remains within the OpISQLServerSecurity tool, and is not pushed into any database local or remote.

  -- CREATE the Database User usrUserName if not already exists
  CREATE USER [usrUserName] FROM LOGIN [usrUserName];
  
  -- Add the Database User as a member of specified role
  ALTER ROLE usrRoleName usrRoleVerb MEMBER usrUserName;
  
  -- GRANT permissions specific to Database User
  grVerb grPermission 
      [grOnObject onObj [onObjectName | rolDatabase /* if onObjectName = onDatabase*/; ] ]
      TO [usrUserName] 
          [WITH GRANT OPTION - if grVerb = GRANT and grGrantOpt = Yes
              | CASCADE -- if grVerb IN(REVOKE | DENY) and grGrantOpt = YES
          ] -- if grGrantOpt = Yes
      [AS onObjectName | usrDatabase /* if onObjectName = onDatabase */
      ] -- if grAS not NULL
  {;]
  

  Finally, this Script will GRANT, REVOKE, or DENY individual Permissions outside of Role memberships, to individual Database Users on each specified usrDatabase. For this, add a row for each such Permission in tblOpIRolesGRANT for each Database User, specifying Permission variables. Specify grRole as the Database User name usrUserName, and specify grRoleClass as "DBUser". This Script will execute each grVerb to GRANT, REVOKE, or DENY each specified Permission to each such specified Database User.

• References

  Database User CREATE Reference