Chapter 2. Configuring Database

Chapter 2. Configuring Database

Our application requires a database. Also, you must have enough permissions to create and edit tables.

You may say to your IT guys the following:

  1. I need a schema in a database.
  2. My team will create tables in this schema.
  3. I want to manage permissions on our tables.
  4. My team members: Nick, Lora, and I am, Alex.
  5. Create two tables, EventHandlers and TableFormats, using the attached SQL codes.

It is an easy task for your IT staff. Just send this chapter to him or her.

Further, we will use the dbo69 schema in the Test2 database and user names like Alex, Nick, and Lora.

You may skip the comments below and continue to the next chapter.

SQL Scripts

The following script creates logins and users in the master database:

USE master
GO

CREATE LOGIN Alex WITH PASSWORD = '1234567890' MUST_CHANGE, CHECK_EXPIRATION=ON;
CREATE LOGIN Nick WITH PASSWORD = '1234567890' MUST_CHANGE, CHECK_EXPIRATION=ON;
CREATE LOGIN Lora WITH PASSWORD = '1234567890' MUST_CHANGE, CHECK_EXPIRATION=ON;
GO

CREATE USER Alex FOR LOGIN Alex;
CREATE USER Nick FOR LOGIN Nick;
CREATE USER Lora FOR LOGIN Lora;
GO

The following script creates users in the Test2 database:

USE Test2
GO

CREATE USER Alex FOR LOGIN Alex WITH DEFAULT_SCHEMA=dbo69;
CREATE USER Nick FOR LOGIN Nick WITH DEFAULT_SCHEMA=dbo69;
CREATE USER Lora FOR LOGIN Lora WITH DEFAULT_SCHEMA=dbo69;
GO

This is the important code that gives permissions to create tables in a database:

GRANT CREATE TABLE ON DATABASE::Test2 TO Alex
GRANT CREATE TABLE ON DATABASE::Test2 TO Nick
GRANT CREATE TABLE ON DATABASE::Test2 TO Lora
GO

The following code creates and configures a role for our team, and grants permissions to Alex:

CREATE ROLE Alex_Team;
GO

ALTER ROLE Alex_Team ADD MEMBER Nick;
ALTER ROLE Alex_Team ADD MEMBER Lora;
GO

GRANT CONTROL ON ROLE::Alex_Team TO Alex;

GRANT CONTROL ON USER::Nick TO Alex;
GRANT CONTROL ON USER::Lora TO Alex; GO

The following code creates a schema and sets the schema permissions:

CREATE SCHEMA dbo69;
GO

GRANT CONTROL ON SCHEMA::dbo69 TO Alex
GO
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, VIEW DEFINITION ON SCHEMA::dbo69 TO Alex_Team
GO

EventHandlers and TableFormats

The following two tables stores the SaveToDB add-in configuration.

You must have these tables in a database to implement certain of features.

Please replace dbo69 in the following code (9 places) to your actual schema:

CREATE TABLE dbo69.EventHandlers (
      ID int IDENTITY(1,1) NOT NULL
    , TABLE_SCHEMA nvarchar(128) NOT NULL
    , TABLE_NAME nvarchar(128) NOT NULL
    , COLUMN_NAME nvarchar(128) NULL
    , EVENT_NAME varchar(50) NOT NULL
    , HANDLER_SCHEMA nvarchar(128) NULL
    , HANDLER_NAME nvarchar(128) NULL
    , HANDLER_TYPE nvarchar(128) NULL
    , HANDLER_CODE nvarchar(MAX) NULL
    , TARGET_WORKSHEET nvarchar(128) NULL
    , MENU_ORDER int NULL
    , EDIT_PARAMETERS bit NULL
    , IS_ACTIVE bit NULL DEFAULT((1))
    , CONSTRAINT PK_EventHandlers_dbo69 PRIMARY KEY (ID)
);
GO

CREATE TABLE dbo69.TableFormats (
      ID int IDENTITY(1,1) NOT NULL
    , TABLE_SCHEMA nvarchar(128) NOT NULL
    , TABLE_NAME nvarchar(128) NOT NULL
    , TABLE_EXCEL_FORMAT_XML xml NULL
    , CONSTRAINT PK_TableFormat_dbo69 PRIMARY KEY (ID)
    , CONSTRAINT IX_TableFormats_Schema_Name_dbo69 UNIQUE (TABLE_NAME, TABLE_SCHEMA)
);
GO
 INSERT INTO dbo69.EventHandlers (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, EVENT_NAME, HANDLER_SCHEMA, HANDLER_NAME, HANDLER_TYPE, HANDLER_CODE) VALUES ('dbo69', 'EventHandlers', 'EVENT_NAME', 'ValidationList', NULL, NULL, 'VALUES', 'Actions,Change,ContextMenu,DoubleClick,SelectionChange,ConvertFormulas,DoNotConvertFormulas,DoNotSelect,DoNotSave,DoNotChange,ProtectRows,Formula,FormulaValue,ValidationList,SelectionList');

INSERT INTO dbo69.EventHandlers (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, EVENT_NAME, HANDLER_SCHEMA, HANDLER_NAME, HANDLER_TYPE, HANDLER_CODE) VALUES ('dbo69', 'EventHandlers', 'HANDLER_TYPE', 'ValidationList', NULL, NULL, 'VALUES', 'TABLE,VIEW,PROCEDURE,FUNCTION,CODE,HTTP,TEXT,MACRO,CMD,VALUES,RANGE,REFRESH,MENUSEPARATOR');
GO
This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.OK