Create table permission

  • SkyBVI

    SSCoach

    Points: 15111

    Hi,

    I am trying to give a user (ccr) to create table permission in 1 of db's.

    I ran this:-

    grant create table to ccr

    it says command done successfully

    BUT

    ccr cant create table now also.

    Then, i ran:-

    grant create table on schema::dbo to ccr

    Then, it says

    Msg 102, Level 15, State 1, Line 0

    Incorrect syntax near 'CREATE TABLE..'.

    How shld i give permissions :

    Regards,

    Sushant

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    Since CCR does not own the dbo schema, you must give permission to ALTER it, too.

    GRANT ALTER ON SCHEMA::dbo TO ccr;

    However, you probably want to create a role, given the permissions to the role, and make ccr a member of the role. For instance:

    CREATE ROLE ModifyTable;

    GO

    GRANT CREATE TABLE TO ModifyTable;

    GRANT ALTER ON SCHEMA::dbo TO ModifyTable;

    GO

    EXEC sp_addrolemember 'ModifyTable', 'ccr';

    GO

    REVOKE CREATE TABLE FROM ccr;

    Now, because a user has ALTER permissions on the schema, he/she can affect existing objects. So you'll have to build a DDL trigger to restrict the role to just being able to touch tables. There are examples of this in the forums and the scripts if you do a search.

    K. Brian Kelley
    @kbriankelley

  • SkyBVI

    SSCoach

    Points: 15111

    @ k brian

    Thanks a lot

    Regards.

    Sushant

  • wangxc16

    SSC Journeyman

    Points: 87

    Did you test it? It looked like not working

  • jlp3630

    Hall of Fame

    Points: 3243

    K. Brian Kelley (5/12/2011)


    Since CCR does not own the dbo schema, you must give permission to ALTER it, too.

    GRANT ALTER ON SCHEMA::dbo TO ccr;

    However, you probably want to create a role, given the permissions to the role, and make ccr a member of the role. For instance:

    CREATE ROLE ModifyTable;

    GO

    GRANT CREATE TABLE TO ModifyTable;

    GRANT ALTER ON SCHEMA::dbo TO ModifyTable;

    GO

    EXEC sp_addrolemember 'ModifyTable', 'ccr';

    GO

    REVOKE CREATE TABLE FROM ccr;

    Now, because a user has ALTER permissions on the schema, he/she can affect existing objects. So you'll have to build a DDL trigger to restrict the role to just being able to touch tables. There are examples of this in the forums and the scripts if you do a search.

    Here's some code for the DDL trigger referenced above:

    -- This trigger fires on all DDL database level events (https://technet.microsoft.com/en-US/library/ms191441(v=SQL.90).aspx)

    -- If the login is a member of the 'ModifyTable' role,

    -- execute their DDL statement only if it is one of the following SQL statements:

    -- 1. CREATE TABLE

    -- 2. DROP TABLE

    -- 3. ALTER TABLE

    -- 4. SELECT INTO

    CREATE TRIGGER db_trigger_BlockNonTableDDL

    ON DATABASE

    FOR DDL_DATABASE_LEVEL_EVENTS

    AS

    BEGIN

    IF IS_MEMBER('ModifyTable') = 1

    BEGIN

    DECLARE @TriggerEventText nvarchar(max);

    SET @TriggerEventText = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

    IF NOT ((@TriggerEventText LIKE 'CREATE TABLE%') OR

    (@TriggerEventText LIKE 'DROP TABLE%') OR

    (@TriggerEventText LIKE 'ALTER TABLE%') OR

    (@TriggerEventText LIKE 'SELECT % INTO %'))

    BEGIN

    RAISERROR (@TriggerEventText, 16, 1)

    ROLLBACK TRANSACTION;

    END

    END;

    END;

    GO

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

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