-- Before executing this script:
-- Replace [_GMSYNC] with the name of your sync database

-- create a blank database to use as the syncronisation database
CREATE DATABASE [_GMSYNC]
GO

-- enable change tracking
USE [master]
GO
ALTER DATABASE [_GMSYNC] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 90 DAYS)
GO


/****** Object:  StoredProcedure [dbo].[usp_SyncBatchAnchor]    create this stored proc if it does not exist ******/
USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

	create procedure [dbo].[usp_SyncBatchAnchor]
	(
		@sync_last_received_anchor bigint,  
		@sync_batch_size int,
		@sync_table_name nvarchar(max),
		@sync_max_received_anchor bigint output,  
		@sync_new_received_anchor bigint output,    
		@sync_batch_count int output
	)
	as
	begin
			-- Set a default batch size if a valid one is not passed in.
		   IF  @sync_batch_size IS NULL OR @sync_batch_size <= 0
			   SET @sync_batch_size = 1000    

			 -- Before selecting the first batch of changes,
			 -- set the maximum anchor value for this synchronization           
			 -- session. After the first time that this procedure is 
			 -- called, Synchronization Services passes a value for 
			 -- @sync_max_received_anchor to the procedure. Batches of 
			 -- changes are synchronized until this value is reached.
		   IF @sync_max_received_anchor IS NULL
			 SELECT  @sync_max_received_anchor = change_tracking_current_version()
       
		   -- If this is the first synchronization session for a database,
		   -- get the lowest change version value from the tables. By 
		   -- default, Synchronization Services uses a value of 0 for 
		   -- sync_last_received_anchor on the first synchronization. If        
		   -- you do not set @sync_last_received_anchor,
		   -- this can cause empty batches to be downloaded until the 
		   -- lowest change version value is reached.
		   IF @sync_last_received_anchor IS NULL
		   BEGIN
				SELECT @sync_last_received_anchor = 1

			-- Changes are only retained in the change table for a limited 
			-- period of time set by the CHANGE_RETENTION parameter 
			-- (on ALTER DATABASE).
			-- Check that we haven't had changes cleaned up on this table
			-- (i.e. CHANGE_TRACKING_MIN_VALID_VERSION returns > 0)        
				IF CHANGE_TRACKING_MIN_VALID_VERSION(
				   object_id(N'dbo.' + @sync_table_name)) > 
					   @sync_last_received_anchor 
				  RAISERROR (N'SQL Server Change Tracking has cleaned up 
								tracking information for table ''%s%s''. To recover from this error, the 
								client must reinitialize its local database and try 
								again',16,3,N'dbo.', @sync_table_name)

				SET @sync_new_received_anchor = 
					@sync_last_received_anchor + @sync_batch_size

				-- Determine how many batches are required during the 
				-- initial synchronization.
				IF @sync_batch_count <= 0
				  SET @sync_batch_count = (
					 (@sync_max_received_anchor / @sync_batch_size) - 
					 (@sync_last_received_anchor /  @sync_batch_size)
				  )
		   END
		 ELSE
		   BEGIN
			 SET @sync_new_received_anchor = 
			   @sync_last_received_anchor + @sync_batch_size

				-- Determine how many batches are required during subsequent 
				-- synchronizations.
				IF @sync_batch_count <= 0
				  SET @sync_batch_count = (
					 (@sync_max_received_anchor / @sync_batch_size) - 
					 (@sync_new_received_anchor /  @sync_batch_size)) + 1 
 
		   END

		 -- Check whether this is the last batch.
		 IF @sync_new_received_anchor >= @sync_max_received_anchor
		 BEGIN
		   SET @sync_new_received_anchor = @sync_max_received_anchor        
		   IF @sync_batch_count <= 0
			 SET @sync_batch_count = 1
		 END
	end
GO

-- Before exectiong this script:
-- change role names if you would prefer names other than below
-- Replace [_GMSYNC] with the name of your sync database

-- Role on master database
USE [master]
GO

-- Create a gm_executor role
CREATE ROLE gm_executor

-- Grant execute rights to the new role on xp_logininfo 
GRANT EXECUTE ON master.sys.xp_logininfo TO gm_executor

--Roles on Sync database
USE [_GMSYNC]
GO

-- Create a gm_executor role
CREATE ROLE gm_executor

-- Grant execute rights to the new role
GRANT EXECUTE TO gm_executor


-- Create a gm_manager role
CREATE ROLE gm_manager

-- Grant execute and Alter rights to the new role
GRANT ALTER TO gm_manager
GRANT EXECUTE TO gm_manager
GO

-- Before executing this script:
-- Replace [PERTH\gbtestgroup2] with the name of your Windows group for Geobank Mobile Managers
-- Replace [_GMSYNC] with the name of your sync database


--If the group does not exist as a login on the server, add the manager group as a login
USE [master]
GO
CREATE LOGIN [PERTH\gbtestgroup2] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO


--Add the usermapping to the Sync Database and add minimum role membership

use [_GMSYNC]
GO


CREATE USER [PERTH\gbtestgroup2] FOR LOGIN [PERTH\gbtestgroup2]  WITH DEFAULT_SCHEMA=[dbo]
GO

ALTER ROLE [db_datareader] ADD MEMBER [PERTH\gbtestgroup2]
GO

ALTER ROLE [db_datawriter] ADD MEMBER [PERTH\gbtestgroup2]
GO

ALTER ROLE [db_ddladmin] ADD MEMBER [PERTH\gbtestgroup2]
GO

ALTER ROLE [gm_manager] ADD MEMBER [PERTH\gbtestgroup2]
GO

-- Before exectiong this script:
-- Replace [PERTH\gbtestgroup] with the name of your Windows group for Geobank mobile users
-- Replace [_GMSYNC] with the name of your sync database

--If the group does not exist as a login on the server, add the user group as a login
USE [master]
GO
CREATE LOGIN [PERTH\gbtestgroup] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

--Add the usermapping to the Sync Database and add minimum role membership
use [_GMSYNC]
GO

CREATE USER [PERTH\gbtestgroup] FOR LOGIN [PERTH\gbtestgroup]  WITH DEFAULT_SCHEMA=[dbo]
GO

ALTER ROLE [db_datareader] ADD MEMBER [PERTH\gbtestgroup]
GO

ALTER ROLE [db_datawriter] ADD MEMBER [PERTH\gbtestgroup]
GO

ALTER ROLE [db_ddladmin] ADD MEMBER [PERTH\gbtestgroup]
GO

ALTER ROLE [gm_executor] ADD MEMBER [PERTH\gbtestgroup]
GO