SQL Server database level roles for creating tables

Question

Is there a way to create or change a special role for creating tables? Our software developer team has db_datareader and db_datawriter roles but they can not create new tables.

I read this article related to database level roles. I don't want to grant db_ddladmin role because there is too much permissions in this role.

So my question is how can i give creating and deleting tables permissions for a database? Views, procedures and functions are okay too. All I need is I don't want to grant permissions related to security, login or backup operations.

Answer 1

If you want to create a role with specific rights, you could do this:

CREATE ROLE CreateObjects
GRANT CREATE TABLE TO CreateObjects
GRANT CREATE VIEW TO CreateObjects
GRANT CREATE FUNCTION TO CreateObjects
GRANT CREATE PROCEDURE TO CreateObjects
GRANT ALTER ANY SCHEMA TO CreateObjects

Create the test user:

CREATE LOGIN testlogin with password = 'StrongP@SSWORD123'

CREATE USER Testlogin FOR LOGIN Testlogin

Add the user to the role:

ALTER ROLE CreateObjects ADD MEMBER Testlogin

Test the user's permissions:

EXECUTE AS LOGIN = 'testlogin'

SELECT SUSER_NAME(),USER_NAME()

(No column name)    (No column name)
testlogin   Testlogin


CREATE TABLE dbo.test(id int)

DROP TABLE dbo.test 
REVERT

Result

Commands completed successfully.

Or like Tom stated, add the user to the db_ddladmin role.

ALTER ROLE db_ddladmin ADD MEMBER testlogin

Answer 2

You can create your own database role for this, and then grant CREATE TABLE at the database level:

GRANT CREATE TABLE TO rolename

You can of course also grant this permission directly to the users, if you wish.