robie2011
11/20/2017 - 1:25 PM

T-SQL SymmetricDS Configuration

--- this script creates and configures permission for BPS and SYM_DATABASE.
--- if BPS Database already exists only permissions for accessing BPS Database is recreated
--- otherwiese it also creates BPS Database
--- SYM_DATABASE itself will be re-created anyway.

--- NOTE: Application User need access to symmetric-database for insert and execution
--- operations caused by trigger.
--- We assume the application user with correct permission exists
--- already and is named 'BPS'

--- author: Robert Rajakone
--- date: 20. Nov 2017
--- ***************************************************************************************


--- CLEANING FOR RECREATION OF SYM USER/DB
---		removing sym-database
---		removing sym_user from bps db (if exists)
---		drop sym_user login


--- delete sym_user from BPS DB
USE master
IF EXISTS(select * from sys.databases where name='BPS')
	USE BPS
	if exists (SELECT * FROM sys.database_principals WHERE name = 'SYM_USER')
		drop user SYM_USER;
GO

--- delete sym_db
use master
IF EXISTS(select * from sys.databases where name='SYM_DATABASE')
	drop database SYM_DATABASE
GO

--- delete sym_user login
If exists (select * from master.dbo.syslogins where name = 'SYM_USER')
drop login SYM_USER
GO


--- create BPS DB
IF NOT EXISTS(select * from sys.databases where name='BPS')
create database BPS
GO

--- create SymmetricDS User
create database SYM_DATABASE
go

CREATE LOGIN SYM_USER
WITH PASSWORD = 'PASSWORD';
GO
use SYM_DATABASE;
GO
CREATE USER SYM_USER FOR LOGIN SYM_USER;
GO
GRANT CREATE TABLE ON DATABASE::SYM_DATABASE to SYM_USER;
GRANT CREATE FUNCTION ON DATABASE::SYM_DATABASE to SYM_USER;
GRANT REFERENCES ON DATABASE::SYM_DATABASE to SYM_USER;
GRANT ALTER, SELECT, INSERT, DELETE, UPDATE ON SCHEMA::dbo TO SYM_USER;
GO

use BPS;
CREATE USER SYM_USER FOR LOGIN SYM_USER
GRANT SELECT, ALTER ON DATABASE::BPS to SYM_USER;


-- Application User
--CREATE LOGIN APP_USER
--WITH PASSWORD = 'APP_USER';
--GO
--use APP_DATABASE;
--GO
--CREATE USER APP_USER FOR LOGIN APP_USER
--GO
--GRANT SELECT, INSERT, DELETE, UPDATE ON SCHEMA::dbo TO APP_USER;
--GO
use SYM_DATABASE;
CREATE USER BPS FOR BPS
GRANT INSERT, EXECUTE ON DATABASE::SYM_DATABASE to BPS;