locked
SQL Server 2005/2008 Private Tables RRS feed

  • Question

  • I want to create tables in such a way as to provide individual users with a set of their own tables based on tables that already exist. I was considering trying to use a schema based solution and hiding the set of user specific tables from each other user. Does anyone know of a pattern or a best practice around this? I have already created a batch based SSIS package that works however occassionally the users use Management Studio and I want them to only be able to see this subset of tables that is there own in both SSIS and Management Studio.

    Thanks

    Wednesday, June 29, 2011 3:47 PM

Answers

  • On Wed, 29 Jun 2011 15:47:26 +0000, PieceOfChum wrote:

    I want to create tables in such a way as to provide individual users with a set of their own tables based on tables that already exist. I was considering trying to use a schema based solution and hiding the set of user specific tables from each other user. Does anyone know of a pattern or a best practice around this? I have already created a batch based SSIS package that works however occassionally the users use Management Studio and I want them to only be able to see this subset of tables that is there own in both SSIS and Management Studio.

    Thanks

    Hi PieceOfChum,

    One possibility might be to create a schema for each user, create
    copies of the tables in all schemas (so you have dbo.Table1 as the
    "master" table; dennis.Table1 for user dennis; mary.Table1 for the
    user Mary, etc). Then grant the users rights on their own schema and
    deny access to all other schemas.

    Give it a shot and see if this works for you.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Wednesday, June 29, 2011 10:18 PM

All replies

  • On Wed, 29 Jun 2011 15:47:26 +0000, PieceOfChum wrote:

    I want to create tables in such a way as to provide individual users with a set of their own tables based on tables that already exist. I was considering trying to use a schema based solution and hiding the set of user specific tables from each other user. Does anyone know of a pattern or a best practice around this? I have already created a batch based SSIS package that works however occassionally the users use Management Studio and I want them to only be able to see this subset of tables that is there own in both SSIS and Management Studio.

    Thanks

    Hi PieceOfChum,

    One possibility might be to create a schema for each user, create
    copies of the tables in all schemas (so you have dbo.Table1 as the
    "master" table; dennis.Table1 for user dennis; mary.Table1 for the
    user Mary, etc). Then grant the users rights on their own schema and
    deny access to all other schemas.

    Give it a shot and see if this works for you.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Wednesday, June 29, 2011 10:18 PM
  • What are you trying to achieve?
    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    Thursday, June 30, 2011 6:39 AM