/****************************************************************************

Name:		ID40_UserCreateScript
Sample:		

Author:		JonG
Purpose:	Create SQL credentials to use for ID40
Used by:	ID40
Required:	v_ZCJ_GLBudgetDetailPivot
			ZCJ_GLBudget

Date		Initials	Project		Description
2022.06.08	JG			ID40		Pointed utilitiy to eAuto db and creating synonyms for CEOJuice DB
2022.05.20	JG			ID40		Initial creation

*****************************************************************************/


--------------------------------------------------------------------------------------------
-- STEP 1: confirm CEOJuice database is corret for  @vs_CEOJuice_DB
-- STEP 2: update e-auto production database name for @vs_Production_DB
-- STEP 3: modify password for @vs_Password
--------------------------------------------------------------------------------------------

declare @vs_CEOJuice_DB	nvarchar(32), @vs_Production_DB	nvarchar(32), @vs_User nvarchar(32), @vs_Password nvarchar(16)

set		@vs_CEOJuice_DB		= 'CEOJuice'		-- STEP 1
set		@vs_Production_DB	= 'PRODUCTION'		-- STEP 2

set		@vs_User			= 'CEOJuice_ID40'	-- STEP 3
set		@vs_Password		= 'PASSWORD'		


--------------------------------------------------------------------------------------------
-- DECLARE SCRIPT PARAMETERS
--------------------------------------------------------------------------------------------		
declare	@sqlLOGIN		nvarchar(4000),
		@sqlUSER		nvarchar(4000),
		@sqlRIGHTS		nvarchar(4000),
		@sqlSynonyms	nvarchar(4000)

--------------------------------------------------------------------------------------------
-- CREATE SQL LOGIN
--------------------------------------------------------------------------------------------
set		@sqlLOGIN = 
		'USE ' 
		+ @vs_CEOJuice_DB 
		+' IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N''' + @vs_User
		+ ''') DROP LOGIN [' + @vs_User    
		+ '] CREATE LOGIN [' +  @vs_User + '] WITH PASSWORD = ''' 
		+ @vs_Password 
		+ ''' , DEFAULT_DATABASE = '
		+ @vs_CEOJuice_DB 
		+ ' , DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
select  @sqlLOGIN
exec	(@sqlLOGIN)
--------------------------------------------------------------------------------------------
-- CREATE PRODUTION DB USER
--------------------------------------------------------------------------------------------
set		@sqlUSER = 
		'USE ' 
		+ @vs_Production_DB 
		+' IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @vs_User +''')    
		DROP USER [' + @vs_User + ']    
		CREATE USER [' + @vs_User +'] 
		FOR LOGIN [' + @vs_User + '] WITH DEFAULT_SCHEMA=[dbo]'
select	@sqlUSER
exec	(@sqlUSER)
--------------------------------------------------------------------------------------------
-- CREATE CEOJUICE DB USER
--------------------------------------------------------------------------------------------
set		@sqlUSER = 
		'USE ' 
		+ @vs_CEOJuice_DB 
		+' IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @vs_User +''')     
		DROP USER [' + @vs_User + ']    
		CREATE USER [' + @vs_User +'] 
		FOR LOGIN [' + @vs_User + '] WITH DEFAULT_SCHEMA=[dbo]'
select	@sqlUSER
exec	(@sqlUSER)

--------------------------------------------------------------------------------------------
-- CREATE SYNONYM for v_ZCJ_GLBudgetDetailPivot -- 2022.06.08
--------------------------------------------------------------------------------------------
set @sqlSynonyms = 
				'Use [' + @vs_Production_DB + '] 

	IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = N''v_ZCJ_GLBudgetDetailPivot'')
	DROP SYNONYM [dbo].[v_ZCJ_GLBudgetDetailPivot]   
	create Synonym  [dbo].[v_ZCJ_GLBudgetDetailPivot] for [' + @vs_CEOJuice_DB + '].[dbo].[v_ZCJ_GLBudgetDetailPivot]'

--select @sqlSynonyms

exec (@sqlSynonyms)
--------------------------------------------------------------------------------------------
-- CREATE SYNONYM for ZCJ_GLBudget -- 2022.06.08
--------------------------------------------------------------------------------------------
set @sqlSynonyms = 
				'Use [' + @vs_Production_DB + '] 

	IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = N''ZCJ_GLBudget'')
	DROP SYNONYM [dbo].[ZCJ_GLBudget]   
	create Synonym  [dbo].[ZCJ_GLBudget] for [' + @vs_CEOJuice_DB + '].[dbo].[ZCJ_GLBudget] ' 

--select @sqlSynonyms

exec (@sqlSynonyms)
----------------------------------------------------------------------------------------------
-- CREATE SQL RIGHTS for PRODUCTION database
----------------------------------------------------------------------------------------------
set		@sqlRIGHTS = 
		'USE ' 
		+ @vs_Production_DB + '  
		GRANT SELECT, UPDATE, INSERT	ON dbo.GLBudgets to ' + @vs_User + '
		GRANT SELECT, UPDATE, INSERT	ON dbo.GLBudgetDetails to ' + @vs_User + '
		GRANT SELECT	ON dbo.GLJournalDetails to ' + @vs_User + '
		GRANT SELECT	ON dbo.GLJournal to ' + @vs_User + '
		GRANT SELECT	ON dbo.GLAccounts to ' + @vs_User + '
		GRANT SELECT	ON dbo.GLAccountTypes to ' + @vs_User + '
		GRANT SELECT	ON dbo.GLDepts to ' + @vs_User + '
		GRANT SELECT	ON dbo.GLACcounts to ' + @vs_User + '
		GRANT SELECT	ON dbo.GLBranches to ' + @vs_User + '
		GRANT EXECUTE	ON dbo.GLBudget_BudgetSetup to ' + @vs_User + '
		GRANT SELECT	ON dbo.v_ZCJ_GLBudgetDetailPivot to ' + @vs_User + '
		GRANT EXECUTE	ON dbo.ZCJ_GLBudget to ' + @vs_User 

select	@sqlRIGHTS
exec	(@sqlRIGHTS)
--------------------------------------------------------------------------------------------
-- CREATE SQL RIGHTS for CEOJUICE database
--------------------------------------------------------------------------------------------
--set		@sqlRIGHTS = 
--		'USE ' 
--		+ @vs_CEOJuice_DB + '  
		
--		--GRANT SELECT, UPDATE, INSERT	ON dbo.GLBudgets to ' + @vs_User + '
--		--GRANT SELECT, UPDATE, INSERT	ON dbo.GLBudgetDetails to ' + @vs_User + '
--		--GRANT SELECT	ON dbo.GLJournalDetails to ' + @vs_User + '
--		--GRANT SELECT	ON dbo.GLJournal to ' + @vs_User + '
--		--GRANT SELECT	ON dbo.GLAccounts to ' + @vs_User + '
--		--GRANT SELECT	ON dbo.GLAccountTypes to ' + @vs_User + '
--		--GRANT SELECT	ON dbo.GLDepts to ' + @vs_User + '
--		--GRANT SELECT	ON dbo.GLACcounts to ' + @vs_User + '
--		--GRANT SELECT	ON dbo.GLBranches to ' + @vs_User + '
--		--GRANT EXECUTE	ON dbo.GLBudget_BudgetSetup to ' + @vs_User + '
--		GRANT SELECT	ON dbo.v_ZCJ_GLBudgetDetailPivot to ' + @vs_User + '
--		GRANT EXECUTE	ON dbo.ZCJ_GLBudget to ' + @vs_User 


-- 2022.06.08
set		@sqlRIGHTS = 
		'USE ' 
		+ @vs_CEOJuice_DB + '  
		
		GRANT SELECT	ON dbo.v_ZCJ_GLBudgetDetailPivot to ' + @vs_User + '
		GRANT EXECUTE	ON dbo.ZCJ_GLBudget to ' + @vs_User 

select	@sqlRIGHTS
exec	(@sqlRIGHTS)