SQL Server 2016 minimum permissions to grant a User CREATE/MODIFY/DROP on all Tables within a DB?

Question

Hello, what would be the minimum permissions script to grant a specific User to CREATE/MODIFY/DROP on all Tables within a DB?

Not allowing them to create/modify/alter any other objects or operations like Views, Stored Procedures, other Users, Security, etc...?

Thanks in advance.

Answer 1

You can find all permissions at

https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-database-permissions-transact-sql?view=sql-server-ver16

For example CREATE TABLE.

Answer 2

As Olaf suggests, you can find the answers yourself by looking up the commands and then find the Permission section (there is a ToC on top to find this section quickly.)

From the top of my head, I thnk that in order to create a table you neeed to have CREATE TABLE permission on database level, and you need ALTER permission in the schema the table is to be created in. If you want the user to be able to create tables in any schema, the user would need ALTER permission on database level.

For ALTER and DROP, I seem to recall that you need ALTER permission on the table. If you have ALTER permission on the schema, that would come through the table.

But as I said, this from the top of my head. To know for sure, I would have to look up the documentation.

To test this out, you can do this:

CREATE USER MyTestUser WITHOUT LOGIN
-- Grant permissions here.
go
EXECUTE AS USER = 'MyTestUser'  -- Impersonate the test usre
go
-- Run some commands that are supposed to work here.
go
-- Run commands that are not supposed to work here,
go
-- with each command in its own batch.
go
REVERT -- Becomd yourself again.
go
DROP USER TestUser
go
-- Drop any test objects created.