-- *************************************************************
-- -------------------------------------------------------------
-- The standard installer adds the local machine administrator group to 
-- either the System Administrator or all Active Navigation roles.
-- 
-- If you cannot access the system as a member of this group then this 
-- script can be used to enable access for additional users
-- 
-- The specified user or group is added to the database to give access the following Active Navigation roles:
-- System Administrator role
-- AN Administrator role
-- Information Manager role
-- 
-- This gives access to all tabs in the Active Navigation UI.

-- Edit this line to configure the entry to be added, specifying either a user or group name to be added
-- to the database. This should be in the form DOMAIN\NAME
DECLARE @userOrGroupName VARCHAR(MAX) = 'an\tester'

-- Edit this line to determine whether the entry added is for a user or a group. Set the value 1 to 
-- add as a user and the value 0 to add as a group.
DECLARE @isUser BIT = 1

-- *************************************************************
-- -------------------------------------------------------------

DECLARE @Today DATETIME = GETDATE()

DECLARE @usersForAllRoles TABLE (anUser NVARCHAR(400), isUser BIT);

INSERT INTO @usersForAllRoles(anUser, isUser) VALUES (@userOrGroupName, @isUser);

DECLARE @sysAdminRoleId NVARCHAR(128);
DECLARE @anAdminRoleId NVARCHAR(128);
DECLARE @infoManagerRoleId NVARCHAR(128);
DECLARE @todaysDate DATETIME

SET @todaysDate = GETUTCDATE();

SELECT @sysAdminRoleId = ar.Id 
FROM AspnetRoles ar 
WHERE ar.Name = 'System Administrator';

SELECT @anAdminRoleId = ar.Id 
FROM AspnetRoles ar 
WHERE ar.Name = 'AN Administrator';
	
SELECT @infoManagerRoleId = ar.Id 
FROM AspnetRoles ar 
WHERE ar.Name = 'Information Manager';
	
PRINT 'Adding entry to System Administrator role'

-- if user
INSERT INTO RoleMapping (MappingId, WindowsUsername, WindowsGroupName, AspNetRoleId, LastModified, Active)
SELECT 
NEWID(), anUser, NULL, @sysAdminRoleId, @todaysDate, 1 
FROM @usersForAllRoles
WHERE isUser = 1;

-- if group
INSERT INTO RoleMapping (MappingId, WindowsUsername, WindowsGroupName, AspNetRoleId, LastModified, Active)
SELECT NEWID(), NULL, anUser, @sysAdminRoleId, @todaysDate, 1 
FROM @usersForAllRoles
WHERE isUser = 0;

PRINT 'Adding entry to AN Administrator role'

-- if user
INSERT INTO RoleMapping(MappingId, WindowsUsername, WindowsGroupName, AspNetRoleId, LastModified, Active)
SELECT NEWID(), anUser, NULL, @anAdminRoleId, @todaysDate, 1 
FROM @usersForAllRoles
WHERE isUser = 1;

-- if group
INSERT INTO RoleMapping(MappingId, WindowsUsername, WindowsGroupName, AspNetRoleId, LastModified, Active)
SELECT NEWID(), NULL, anUser, @anAdminRoleId, @todaysDate, 1 
FROM @usersForAllRoles
WHERE isUser = 0;

PRINT 'Adding entry to Information Manager role'

-- if user
INSERT INTO RoleMapping(MappingId, WindowsUsername, WindowsGroupName, AspNetRoleId, LastModified, Active)
SELECT NEWID(), anUser, NULL, @infoManagerRoleId, @todaysDate, 1 
FROM @usersForAllRoles
WHERE isUser = 1;

-- if group
INSERT INTO RoleMapping (MappingId, WindowsUsername, WindowsGroupName, AspNetRoleId, LastModified, Active)
SELECT NEWID(), NULL, anUser, @infoManagerRoleId, @todaysDate, 1 
FROM @usersForAllRoles
WHERE isUser = 0;