2

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

1
  • Please be aware that there are SERIOUS security issues with this kind of access. Basically you are granting anyone who can access the SQL instance the ability to do anything with those tables, including drop them, or truncate all the data.
    – Nic
    Dec 19, 2017 at 17:27

2 Answers 2

2

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)

-4

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
3
  • Your answer is the syntax for MySQL and not for Microsoft SQL Server although they are similar.
    – John K. N.
    Dec 19, 2017 at 8:01
  • also I just want to grand create and alter permissions, not all other privileges? Dec 19, 2017 at 8:33
  • @PrashantLuhar Lookup Error - SQL Server Database Error: Incorrect syntax near 'CREATE'. Dec 19, 2017 at 8:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.