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

techresearch7777777 1,801 Reputation points
2024-06-03T16:53:01.3966667+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,125 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Olaf Helper 42,576 Reputation points
    2024-06-03T17:54:12.75+00:00
    0 comments No comments

  2. Erland Sommarskog 104.2K Reputation points MVP
    2024-06-03T21:19:31.01+00:00

    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.