Post reply

Create Schema For Users Accessing Via AD Group

  • September 14, 2010 at 9:47 am

    #227774

    I have created a login for an AD group to one of my SQL instances.

    CREATE LOGIN [DOMAIN\GroupName] FROM WINDOWS

    I then created a user in a database from that AD group, so now everyone in that group has access to the given database:

    USE DatabaseName

    GO

    CREATE USER [DOMAIN\GroupName] FROM LOGIN [DOMAIN\GroupName]

    Now I want to create a schema for an individual user within that group, let's say the user is Domain\MJones.

    MJones has access within the given DB and I want a schema that he owns that no one else can touch. When I try CREATE SCHEMA MJones AUTHORIZATION [DOMAIN\MJones], I get an error stating that user [DOMAIN\MJones] does not exist, which it technically does not. I can do CREATE SCHEMA MJones AUTHORIZATION [DOMAIN\GroupName], but then doesn't everyone in that group have control over this new schema? I ONLY want MJones to have control.

    Am I forced to add the individual users for anyone who need their own schema? That would seem awfully redundant.

    What am I missing?

    Thanks!

  • September 14, 2010 at 9:55 am

    #1220796

    I'm slightly confused. What are you trying to accomplish here? You can grant people the right to build their own schemas, in which case they'd build objects themselves without you making them their own schemas.

  • September 14, 2010 at 10:04 am

    #1220802

    Well then maybe there is a question within a question here.

    There are 4 developer accounts contained in the aforementioned group. All 4 need to be able to create their own objects (tables, views, sp, etc) but only SELECT from each others tables.

    I know each user can create their own objects, but in order to allow for others to access those objects, permissions would need to be granted everytime a new object is created. To avoid that, I thought creating a schema for each user to house their objects in would be most efficient. That way I can just grant SELECT (or whatever permissions) at the SCHEMA level and it will cover any objects they create.

    Am I making this harder than it needs to be?

  • September 15, 2010 at 1:36 pm

    #1221638

    Anyone have any thoughts?

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

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