1

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.

  • 1
    Should they be able to only create tables or can they also create views, procedures, functions etc? – Tom V - Team Monica Dec 19 '18 at 11:00
  • Thanks for your answer. 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. – kojirosan Dec 19 '18 at 11:06
  • 1
    Then why is db_ddladmin too much? – Tom V - Team Monica Dec 19 '18 at 11:08
  • May db_ddladmin database level role edit or delete database audits? If answer is quite ok, db_ddladmin looks suitable for us. Also is it best practice to grant software developers this level role? Thanks again. – kojirosan Dec 19 '18 at 11:20
3

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
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.

  • 1
    This is not sufficient to create a table. Try to create a user, grant him only CREATE TABLE, and make him a member of db_datareader, db_datawriter. When this user attempts to create a table, he'll got the error 2760, because he has no permission on schema – sepupic Dec 19 '18 at 12:40
  • Yes, indeed. Thanks for catching that. ALTER permission for the schema is also required. Just as Randi proposed (in the above reply that I didn't when I wrote mine :-) ). – Tibor Karaszi Dec 19 '18 at 12:45

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.