Grant Permission to Create Tables - SQL Server

Grant Permission to CREATE tables - SQL Server

When I googled, I got right to TechNet. It looks like you want:

GRANT CREATE TABLE

As in:

USE AdventureWorks2012;
GRANT CREATE TABLE TO MelanieK;
GO

How to GRANT CREATE TABLE TO ROLE in SQL Server?

If you are using SQL Server, you would need one more:

GRANT ALTER ON SCHEMA::DBO TO [ROLETEST]

Please find more details HERE.

SQL Server - Give user permission to create table in their own schema, but not in dbo schema

Granting CREATE TABLE, and other permissions doesn't do anything is that USER doesn't also have the ALTER permission. As such, all you need to is GRANT the USER the CREATE TABLE, CREATE VIEW, etc permissions on their specific schema, and then SELECT on the dbo schema.

This uses with WITHOUT LOGIN as an example, as I don't have the LOGIN objects, but this demonstrates the granted, and implicitly denied, permissions:

CREATE DATABASE SampleDB;
GO
USE SampleDB;
GO

CREATE TABLE dbo.SomeTable (SomeID int IDENTITY);

INSERT INTO dbo.SomeTable
DEFAULT VALUES;
GO

CREATE USER student1 WITHOUT LOGIN;
GO
CREATE SCHEMA st1 AUTHORIZATION student1;
GO
GRANT SELECT ON SCHEMA::dbo TO student1;
GRANT CREATE TABLE, CREATE VIEW, CREATE FUNCTION, CREATE PROCEDURE TO student1;
GO

CREATE USER student2 WITHOUT LOGIN;
GO
CREATE SCHEMA st2 AUTHORIZATION student2;
GO
GRANT SELECT ON SCHEMA::dbo TO student2;
GRANT CREATE TABLE, CREATE VIEW, CREATE FUNCTION, CREATE PROCEDURE TO student2;
GO

EXECUTE AS USER = 'student1';
GO

CREATE TABLE st1.TestTable (ID int);
INSERT INTO st1.TestTable
SELECT SomeID
FROM dbo.SomeTable;
GO

SELECT *
FROM st1.TestTable;
GO

REVERT;
GO

EXECUTE AS USER = 'student2';
GO

CREATE PROC st2.TestProc AS
BEGIN
    SELECT *
    FROM st1.TestTable;
END;
GO

EXEC st2.TestProc; --fails
GO
CREATE TABLE st1.TestTable (ID int); --fails too
GO

REVERT;
GO

USE master;
GO

DROP DATABASE SampleDB;

What privileges are needed to create\delete tables on a Microsoft SQL Server? Is dbowner ok?

Just to understand first the benefits of using roles :

• Roles are a part of the tiered security model:
• Login security—Connecting to the server
• Database security—Getting access to the database
• Database objects—Getting access to individual database objects and
  data**

Predefined database roles
You may need to create your own, but you have access to several predefined database roles:

• db_owner: Members have full access.
• db_accessadmin: Members can manage Windows groups and SQL Server
  logins.
• db_datareader: Members can read all data.
• db_datawriter: Members can add, delete, or modify data in the tables.
• db_ddladmin: allows a user to create, drop, or modify any objects within a database, regardless of who owns.
• db_securityadmin: Members can modify role membership and manage
  permissions.
• db_bckupoperator: Members can back up the database.
• db_denydatareader: Members can’t view data within the database.
• db_denydatawriter: Members can’t change or delete data in tables or
  views.

Fixed roles :
The fixed server roles are applied serverwide, and there are several predefined server roles:

• SysAdmin: Any member can perform any action on the server.
• ServerAdmin: Any member can set configuration options on the server.
• SetupAdmin: Any member can manage linked servers and SQL Server
  startup options and tasks.
• Security Admin: Any member can manage server security.
• ProcessAdmin: Any member can kill processes running on SQL Server.
• DbCreator: Any member can create, alter, drop, and restore databases.
• DiskAdmin: Any member can manage SQL Server disk files.
• BulkAdmin: Any member can run the bulk insert command.

SQL Granting create table permissions

Read this
SQL Server grand permission

GRANT CREATE ON SCHEMA :: databaseName TO userName_3;

For MySQL

GRANT CREATE ON databaseName.* TO userName_3;

You can't use the TABLE in the query

Table Privileges

Table privileges apply to all columns in a given table. To assign
table-level privileges, use ON db_name.tbl_name syntax:

GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost'; GRANT SELECT, INSERT
ON mydb.mytbl TO 'someuser'@'somehost'; If you specify tbl_name rather
than db_name.tbl_name, the statement applies to tbl_name in the
default database. An error occurs if there is no default database.

The permissible priv_type values at the table level are ALTER, CREATE
VIEW, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, REFERENCES,
SELECT, SHOW VIEW, TRIGGER, and UPDATE.

Table-level privileges apply to base tables and views. They do not
apply to tables created with CREATE TEMPORARY TABLE, even if the table
names match. For information about TEMPORARY table privileges, see
Section 13.1.18.3, “CREATE TEMPORARY TABLE Syntax”.

MySQL stores table privileges in the mysql.tables_priv table.

SQL Server adding access for user to create table

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.

what privilege is needed for a user to create table in another schema

The user would need the CREATE privilege.

grant usage, create on schema tony to rafal;

---

Related Topics