Monitor 10.11 | Configuring Monitor | Granting Users Access to Monitor | Granting Users Permissions for Non-DBO Schemas | Example
 
Example
Example on assigning permissions for non-dbo schemas.
The following data is used in the example:
*Database Name forISCORE/PROCESS SCHEMA : MYBPMDB
*Database name for archival : MYBPMDB_ARCHIVE
*Integration Server core login name : MYBPMDB_USER1
*Integration Server core username : MYBPMDB_USER1
*Integration Server core schema name : dbo
*Archive login name : MYBPMDB_ARCHIVE_LOGIN1
*Archive username : MYBPMDB_ARCHIVE_USER1
*Archive schema name : MYBPMDB_ARCHIVE_SCHEMA1
1. 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>,