Skip to content

Instantly share code, notes, and snippets.

@levi-turner

levi-turner/CreateQlikUsers_v0.0.1-mssql.sql

Last active August 3, 2017 15:50
Show Gist options
  • Save levi-turner/b8d4b17d06470b90c7727263c5b5c6c1 to your computer and use it in GitHub Desktop.
Save levi-turner/b8d4b17d06470b90c7727263c5b5c6c1 to your computer and use it in GitHub Desktop.
Download ZIP
Create Users for Qlik Sense in Microsoft SQL
Raw
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');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment