-- *************************************************************
-- -------------------------------------------------------------
-- 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,   -- this gives access to all tabs in the Active Navigation UI :
--
-- System Administrator role
-- AN Administrator role
-- Information Manager role
-- 
-- 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) = 'DOMAIN\USERORGROUP'

-- 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 @ApplicationId UNIQUEIDENTIFIER
DECLARE @UserId UNIQUEIDENTIFIER
DECLARE @Today DATETIME = GETDATE()

SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE ApplicationName = '/'

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

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

DECLARE @sysAdminRoleId UNIQUEIDENTIFIER;
DECLARE @anAdminRoleId UNIQUEIDENTIFIER;
DECLARE @infoManagerRoleId UNIQUEIDENTIFIER;
DECLARE @todaysDate DATETIME

SET @todaysDate = GETUTCDATE();

SELECT @sysAdminRoleId = ar.RoleId 
FROM aspnet_Roles ar 
WHERE ar.RoleName = 'System Administrator';

SELECT @anAdminRoleId = ar.RoleId 
FROM aspnet_Roles ar 
WHERE ar.RoleName = 'AN Administrator';
	
SELECT @infoManagerRoleId = ar.RoleId 
FROM aspnet_Roles ar 
WHERE ar.RoleName = '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;