Post reply

Grant permission to Create table in Schema but not other schemas in DB

  • September 14, 2012 at 10:05 am

    #281543

    I've granted ALTER to the schema, but user still can't create table.

    CREATE TABLE ato.test1

    (First_Name char(50),

    Last_Name char(50))

    Error is: CREATE TABLE permission denied in database 'NameOfDatabase'.

    How to grant permission to create a table in one schema, however not any where else within the database?

    We have always used dbo --the default schema-- and now creating schemas to avoid additional databases so new adventure here.

    Thanks.

  • September 14, 2012 at 12:14 pm

    #1538063

    CREATE TABLE is a control permission, not an alter permission. Alter is implied by having control.

    Use this:

    GRANT CONTROL ON SCHEMA::<mySchema> TO <myLogin>

    You can only use the "CREATE TABLE" permission specifically when you are not stating a schema, which leads me to believe that it's an "all schemas or no schemas" type of permission.

    Also verify that the user's default schema is the schema upon which (s)he is creating tables.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 2 posts - 1 through 1 (of 1 total)

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