SQL Server: grant all 'public' users the permission to create tables

Question

I have a set of users who have the 'public' role.

According to the default settings, they can read, update, insert data inside existing tables.

How can I change the database settings to that they can as well create and ALTER tables of schema dbo?

If I click on "Alter role" for the database>decurity>Roles, there is a section 'extended properties" but I am not sure how to use this?

Thank you

Answer 1

Ensure you are in the correct database and then issue the following command:

GRANT CREATE TABLE TO public
GO

If you already have objects that require public to ALTER the table then you can add the following for each existing table:

GRANT ALTER ON OBJECT::dbo.your_table_name TO public
GO

Reference: GRANT (Transact-SQL)

Answer 2

GRANT ALL PRIVILEGES ON *.* TO ''@'localhost' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

The manual says:

In this case, any user who connects from the local host with the correct password for the anonymous user will be permitted access, with the privileges associated with the anonymous-user account.

So for granting create and alter permissions for MS SQL, the syntax will be:

GRANT ALTER ON dbo TO public;

According to SQL Server 2008 help:

Requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.

Granting permission to create tables to a specific user in a specific database not only requires CREATE TABLE permissions but also requires ALTER permissions to the schema, e.g. DBO.

USE db_mydatabase
GRANT ALTER ON Schema :: DBO TO user_mydatabase
GRANT CREATE TABLE TO user_mydatabase
GO