I own a new DB and I have multiple schemas setup and users have different group accesses to ensure they can only edit the schemas they need access to. Recently I gave alter and create access to a user for a schema and the user still can't insert a table. I do my user maintenance through SSMS. What am I doing wrong? Would I be better off to execute a SQL script versus using the GUI in SSMS? Any help would be greatly appreciated!!

The steps I used to grant access initially was as follows, I have a database role permission with the Schema on the securables tab, I then checked the boxes next to Alter, Control, Create, Delete, Execute, Insert, Select, and Update. Then I added users to this DB Role. The users can select but not insert/create a table.

The SQL script the user tried is as follows:

CREATE TABLE Database.Schema.test_table (
oid numeric PRIMARY KEY
,columnEX numeric NOT NULL
,columnEX2 numeric
);

The error message received was as follows:

CREATE TABLE permission denied in database 'database'.

CodePudding user response：

To create a table a user requires both CREATE TABLE permission in the database and ALTER on the target schema.

And it's a strong security best practice to make the user (or group) the owner of the target schema, rather than granting them ALTER or CONTROL on a schema owned by another user. If you grant a user the rights to ALTER or CONTROL a schema owned by a privileged user (like dbo), then they can use ownership chains to access any object in any schema owned by that privileged user.