Create table permission

• SkyBVI

  SSCoach

  Points: 15128

  More actions

  May 11, 2011 at 2:46 pm

  #236009

  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

  Regards
  Sushant Kumar
  MCTS,MCP

• K. Brian Kelley

  SSC Guru

  Points: 114704

  More actions

  May 12, 2011 at 12:13 pm

  #1323896

  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: 15128

  More actions

  May 12, 2011 at 12:45 pm

  #1323912

  @ k brian

  Thanks a lot

  Regards.

  Sushant

  Regards
  Sushant Kumar
  MCTS,MCP

• wangxc16

  SSC Journeyman

  Points: 87

  More actions

  October 18, 2013 at 12:36 pm

  #1659824

  Did you test it? It looked like not working

• jlp3630

  Hall of Fame

  Points: 3243

  More actions

  September 10, 2015 at 10:57 am

  #1825758

  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