Create Users for Qlik Sense in Microsoft SQL

CreateQlikUsers_v0.0.1-mssql.sql

/* ##############################################################################################################################
   Script Name: CreateQlikUsers
   Description: the script is intended to create a tables and rows of users in MS SQL which will can be used as an ODBC UDC in Qlik Sense
   Revision history:
   Version     Date         Author    		Change Notes
   # 0.0.1     2017-07-01   Levi Turner 	Initial version
   ##############################################################################################################################
 */

 -- Step 1. Create necessary database (Optional)
 -- TODO: Error Checking

CREATE DATABASE QlikUsers;

-- Step 2. Create the Users and Attributes tables
-- These will be minimal tables. Additional columns, example a primary key on ID is not within the scope here but possible
-- TODO : Error Checking
/*
IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('QlikGroupUsers'))
BEGIN;
    DROP TABLE [QlikGroupUsers];
END;
GO
*/

USE [QlikUsers]
GO

CREATE TABLE users_basic (
    userid varchar(255) NOT NULL,
    name varchar(255) NOT NULL
);

USE [QlikUsers]
GO

CREATE TABLE attributes_basic (
    userid varchar(255) NOT NULL,
    type varchar(255) NOT NULL,
    value varchar(255) NOT NULL
);

-- Step 3. INSERT rows into the Users table
USE [QlikUsers]
GO

INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('001','Ezekiel Sweet');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('002','Hosea Viafara');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('003','Davina Persky');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('004','Kyle Cadice');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('005','Roselia Balkus');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('006','Randall Zinck');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('007','Sam Franchette');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('008','Jayne Jefferies');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('009','Rebeca Kriskovich');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('010','Reda Slough');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('011','Charlette Wheatley');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('012','Celina Nunley');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('013','Anneliese Beaudry');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('014','Teresita Parsell');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('015','Gabriele Warsham');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('016','Jacques Haugaard');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('017','Antione Domenech');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('018','Renaldo Salz');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('019','Kathie Kaai');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('020','Ethel Demian');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('021','Jamila Klepfer');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('022','Aura Doro');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('023','Yaeko Siverd');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('024','Madonna Sharlow');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('025','Luanne Taula');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('026','Libby Kutch');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('027','Donnette Marschall');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('028','Shaunna Devereux');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('029','Mercy Kuzmish');
INSERT INTO [dbo].[users_basic] ([userid],[name]) VALUES('030','Margeret Turbin');

-- Step 4. INSERT rows into the Attributes table
-- Group (Sales / HR / IT / Executive)
-- Location (AMERICAS / EMEA / APAC)
-- Role (Executive / Developer / Consumer / Administrator)
-- TODO: Cross check coverage
-- 
USE [QlikUsers]
GO
/* 
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('0','Group','');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('0','Location','');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('0','Role','');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('0','Role','');
*/

INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('001','Group','Sales');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('001','Location','AMERICAS');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('001','Role','Consumer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('002','Group','Sales');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('002','Location','AMERICAS');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('002','Role','Developer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('003','Group','HR');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('003','Location','AMERICAS');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('003','Role','Consumer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('004','Group','IT');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('004','Location','AMERICAS');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('004','Role','Administrator');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('004','Role','Developer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('005','Group','HR');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('005','Location','AMERICAS');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('005','Role','Developer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('006','Group','Executive');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('006','Location','AMERICAS');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('006','Location','EMEA');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('006','Location','APAC');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('006','Role','Executive');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('007','Group','IT');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('007','Location','AMERICAS');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('007','Role','Administrator');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('007','Role','Executive');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('007','Role','Developer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('008','Group','Sales');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('008','Location','AMERICAS');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('008','Role','Consumer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('009','Group','Sales');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('009','Location','AMERICAS');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('009','Role','Developer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('009','Role','Administrator');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('010','Group','Sales');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('010','Location','AMERICAS');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('010','Role','Consumer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('011','Group','IT');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('011','Location','EMEA');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('011','Role','Administrator');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('012','Group','IT');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('012','Location','EMEA');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('012','Role','Administrator');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('012','Role','Developer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('013','Group','HR');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('013','Location','EMEA');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('013','Role','Consumer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('014','Group','HR');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('014','Location','EMEA');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('014','Role','Developer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('015','Group','HR');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('015','Location','EMEA');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('015','Role','Executive');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('016','Group','Sales');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('016','Location','EMEA');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('016','Role','Consumer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('017','Group','Sales');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('017','Location','EMEA');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('017','Role','Executive');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('017','Role','Developer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('018','Group','Sales');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('018','Location','EMEA');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('018','Role','Developer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('018','Role','Administrator');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('019','Group','Sales');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('019','Location','EMEA');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('019','Role','Consumer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('020','Group','Executive');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('020','Location','EMEA');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('020','Role','Executive');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('021','Group','IT');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('021','Location','APAC');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('021','Role','Administrator');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('021','Role','Developer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('022','Group','IT');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('022','Location','APAC');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('022','Role','Administrator');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('022','Role','Executive');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('023','Group','HR');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('023','Location','APAC');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('023','Role','Consumer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('024','Group','HR');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('024','Location','APAC');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('024','Role','Developer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('025','Group','HR');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('025','Location','APAC');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('025','Role','Developer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('025','Role','Administrator');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('026','Group','Sales');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('026','Location','APAC');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('026','Role','Consumer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('027','Group','Sales');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('027','Location','APAC');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('027','Role','Developer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('028','Group','Sales');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('028','Location','APAC');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('028','Role','Developer');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('029','Role','Administrator');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('030','Group','Executive');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('030','Location','APAC');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('030','Role','Executive');
INSERT INTO [dbo].[attributes_basic] ([userid],[type],[value]) VALUES('030','Role','Administrator');