Post reply

Creating DB Schema

  • June 23, 2019 at 4:34 pm

    #3656706

    Hi,

    We have created Data Warehouse DB and now planning to create different schema based on different applications so we have application related objects and data into one schema. Our Application/objects are in different server which will be loading into DW server DB.

    We have HR, Finance and Employee applications which are stored in a databases like HRDB, FinanceDB and EmployeeDB.

    We have windows AD group created and assign the needed privileges in Development for all the developers. So each user are part of that windows AD group . After creating DBs, we need to create Schema so we can copy the few objects into application based schema.

    Ex. HR tables will go in HR Schema, Finance related tables will go in FINANCE schema and Employee related tables will go in EMP Schema from there respected tables.

    When i create the Schema using following sql, is it go under my login as i am connecting through my windows authentication?

    Do i need to assign any Role or user privileges to this schema?

    My windows login is part of Developers windows group.

    USE EmployeeDetails

    GO

    CREATE SCHEMA EMP_SCHEMA

    CREATE SCHEMA HR_SCHEMA

    CREATE SCHEMA FINANCE_SCHEMA

     

    Thanks for your help!

  • June 23, 2019 at 4:49 pm

    #3656707

    The AD group should have "connect" privilege by modifying the properties of the database (for each DB). Then, you would need to create role(s) and assign read/write/view definition/etc. for the role and hence everyone in that role will have the same privilege.

    You can drop users (ad account / sql account) in the role's bucket if  you will.

    I hope this helps if I understood your question correctly.

    Cheers,
    John Esraelo

  • June 23, 2019 at 5:46 pm

    #3656713

    Thanks.

    So i should use windows Group login and then create the Schema or i can use my windows login (i am part of that windows group) and create the schema?

    That AD Group has most of the privileges.

  • June 24, 2019 at 7:14 am

    This was removed by the editor as SPAM

  • June 27, 2019 at 11:17 am

    #3658039

    Thank you all for your help!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply