Create and Alter Table Permission only for specific schema

  • shadow_2

    Say Hey Kid

    Points: 713

    Hello experts,

    I need advice on how to grant create table, alter table and create procedure permissions on a specific schema to a specific user.

    I set the user to be the schema owner and thought this would suffice - but it doesn't.

    Thanks in advance,


  • Jeffrey Williams 3188

    SSC Guru

    Points: 88020

    Explicitly grant permissions on the schema, for example:

    GRANT EXECUTE ON SCHEMA::myschema TO myroleoruser;

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]

  • shadow_2

    Say Hey Kid

    Points: 713

    I'll give it a try on monday, thank you Jeffrey.


  • SQL_Helper

    SSC Eights!

    Points: 837


    I want to grant privileges in sql server 2005 to a db user to create and alter tables.

    can anyone helpe me on this.


  • ejoell 66477

    Right there with Babe

    Points: 748

    Grant create table to username

    Grant alter to username

  • daves127

    SSC Rookie

    Points: 40

    Jeffrey Williams 3188 - Friday, July 9, 2010 1:47 PM

    Explicitly grant permissions on the schema, for example:GRANT EXECUTE ON SCHEMA::myschema TO myroleoruser;

    "GRANT CREATE TABLE ON SCHEMA::."... is not valid.

  • Jeff Moden

    SSC Guru

    Points: 993884

    ejoell 66477 - Wednesday, September 28, 2011 2:22 PM

    Grant create table to usernameGrant alter to username

    GRANT Perpetual Headaches to DBA

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

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

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