Sql-server – SQL Server database level roles for creating tables

permissionsrolesql server

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.

Best Answer

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

Related Solutions

SQL Server 2008 R2 – Grant Role in One Database Access to Tables in Another Database

Database roles are security principals that are wholly contained within their respective database and are not shared or visible to other databases. So any roles and users that are in database X have no knowledge of database Y. To accomplish your goal, you'll need to recreate the role in database Y and add all the appropriate users to this database and role. Fortunately, you can script out this process using the system views:

USE [Y];
CREATE ROLE foo;

--grant all perms on foo

--Use this generate sql to add all your users to the role
select 'CREATE USER '+quotename(u.name)+';' + char(10)+
       'EXEC sp_addrolemember ''foo'','''+u.name+''';'
from (select name,principal_id
        from x.sys.database_principals where type = 'R') r
    join x.sys.database_role_members rm 
        on (r.principal_id = rm.role_principal_id)
    join (select name,type_desc,principal_id
        from x.sys.database_principals where type != 'R') u 
        on (rm.member_principal_id = u.principal_id )
where r.name = 'foo'

You can then take this sort of script/process to a batch job to keep things synchronized, but you will need some sort of external process to keep these roles in sync.

One piece that might be confusing here is that you have logins and other server level principals that are connected to the database principals, but there is a clear distinction between these. I provide some explanation about the differences between these principals in this answer

SQL Server – Proper Permissions for ETL User

Your thoughts are to the point.

The only thing I want to suggest is not to use the db_ddladmin account if you only want to truncate tables. The minimum permission for truncating a table is ALTER, see Truncate Table. So you could make a custom database role with ALTER permissions on all tables you need to truncate. Assign that role to the ETLUser account.

A proxy account is in my opinion indeed the best way to run the package under the ETLUser account in an SQL Server agent job.

Best Answer

Related Solutions

SQL Server 2008 R2 – Grant Role in One Database Access to Tables in Another Database

SQL Server – Proper Permissions for ETL User

Related Question