Example
Example on assigning permissions for non-dbo schemas.
The following data is used in the example:
Database Name forISCORE/PROCESS SCHEMA :
MYBPMDBDatabase name for archival :
MYBPMDB_ARCHIVEIntegration Server core login name :
MYBPMDB_USER1 Integration Server core username :
MYBPMDB_USER1 Integration Server core schema name :
dbo Archive login name :
MYBPMDB_ARCHIVE_LOGIN1Archive username :
MYBPMDB_ARCHIVE_USER1Archive schema name :
MYBPMDB_ARCHIVE_SCHEMA11. Create login named MYBPMDB_ARCHIVE_LOGIN1 with default database as MYBPMDB_ARCHIVE using MS SQL Studio.
2. Create Schema and add user in database MYBPMDB_ARCHIVE for login MYBPMDB_ARCHIVE_LOGIN1
USE MYBPMDB_ARCHIVE
GO
CREATE SCHEMA MYBPMDB_ARCHIVE_SCHEMA1
GO
CREATE USER [MYBPMDB_ARCHIVE_USER1] FOR LOGIN [MYBPMDB_ARCHIVE_LOGIN1] WITH DEFAULT_SCHEMA=
[MYBPMDB_ARCHIVE_SCHEMA1]
GO
ALTER AUTHORIZATION ON SCHEMA::MYBPMDB_ARCHIVE_SCHEMA1 TO MYBPMDB_ARCHIVE_USER1
GO
ALTER USER MYBPMDB_ARCHIVE_USER1 WITH DEFAULT_SCHEMA = MYBPMDB_ARCHIVE_SCHEMA1
GO
GRANT CREATE TABLE TO MYBPMDB_ARCHIVE_USER1
GO
GRANT ALTER TO MYBPMDB_ARCHIVE_USER1
GO
GRANT CREATE VIEW TO MYBPMDB_ARCHIVE_USER1
GO
GRANT CREATE PROCEDURE TO MYBPMDB_ARCHIVE_USER1
GO
GRANT EXECUTE TO MYBPMDB_ARCHIVE_USER1 WITH GRANT OPTION
GO
GRANT REFERENCES TO MYBPMDB_ARCHIVE_USER1
GO
3. Using the Database Component Configurator, create an Archive component using MYBPMDB_ARCHIVE_LOGIN1 as User ID in the MYBPMDB_ARCHIVE database.
4. Add archive user in ISCore database so that it can access the main database and grant permission.
USE [MYBPMDB]
GO
CREATE USER MYBPMDB_ARCHIVE_USER1 FOR LOGIN MYBPMDB_ARCHIVE_LOGIN1 WITH DEFAULT_SCHEMA=dbo
GO
GRANT CONTROL ON SCHEMA::dbo TO MYBPMDB_ARCHIVE_USER1
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: dbo TO MYBPMDB_ARCHIVE_USER1;
GO
GRANT CREATE TABLE TO MYBPMDB_ARCHIVE_USER1
GO
GRANT ALTER TO MYBPMDB_ARCHIVE_USER1
GO
GRANT CREATE VIEW TO MYBPMDB_ARCHIVE_USER1
GO
GRANT CREATE PROCEDURE TO MYBPMDB_ARCHIVE_USER1
GO
GRANT EXECUTE TO MYBPMDB_ARCHIVE_USER1 WITH GRANT OPTION
GO
GRANT REFERENCES TO MYBPMDB_ARCHIVE_USER1
GO
5. Update schema names in OPERATION_PARAMETER as follows:
Run set operation parameter service and set Integration Server core schema and Process schema, for example, <databasename>.<schemaname>,