Sql Server db_ddladmin

Question

Good afternoon.

I usually use the role db_ddladmin for my users of database, but i need to know, if i can to apply this role for one user in only one table. Does this is possible?

Thanks,
Marcos Aurelio G Guarnier

Answer 1

Hi,

db_ddladmin allow to run any Data Definition Language (DDL) command in a database.

If you already have a table and you only need permissions to ALTER the table then you can add specific permission for tasks like for example to ALTER MASKED you will need ALTER ANY MASK and you can add the ALTER TABLE permissions.

ALTER TABLE permissions apply to both tables involved in an ALTER TABLE SWITCH statement. Any data that's switched inherits the security of the target table.

For more information please check the documentation about ALTER table and go over all the information regarding permission (search for it and not only the section of Permissions.

https://learn.microsoft.com/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699#permissions

For example this might cover what you need as start:

GRANT UPDATE,INSERT,DELETE,SELECT,ALTER ON dbo.Table_Name TO Your_User_Name;

Note!!!

ALTER TABLE permission allows other permissions through ownership chaining. The user them for example will be able to CREATE/Disabling/ALTER triggers, constraints, columns and computed columns and so on. Using these options they are able to see other sensitive data which is not in the specific table. Therefore, use it with curful and make sure you have a DENY permission if needed.

Answer 2

It is not possible to apply the db_ddladmin role to a user for a specific table. The db_ddladmin role grants the user permissions to perform all data definition language (DDL) operations in a database. This includes the ability to create, alter, and drop tables, as well as other database objects.

If you want to restrict a user's permissions to a specific table, you can do so by creating a user-defined database role and granting the appropriate permissions to that role for the specific table. Then, you can add the user to the user-defined role. This will allow the user to perform only the actions that have been granted to the role for the specified table.

For example, to create a role that has SELECT, INSERT, UPDATE, and DELETE permissions on a table called mytable, you can use the following T-SQL code:

CREATE ROLE myrole;  
GRANT SELECT, INSERT, UPDATE, DELETE ON mytable TO myrole;  

Then, to add a user myuser to the myrole role, you can use the following T-SQL code:

EXEC sp_addrolemember 'myrole', 'myuser';  

This will allow the user myuser to perform SELECT, INSERT, UPDATE, and DELETE operations on the mytable table, but will not allow them to perform any other DDL operations in the database.

If this answer is helpful, please click ^ to vote. Thank you :-)

Answer 3

Hi Pituach, thanks for your help.

But i do not explained all my problem. I add my users at role db_ddladmin because i need that this users have the permissions to create other tables. I already use what you suggest sometimes, i forgot to explain this. I need to give for one user the permission to alter, create, delete and create too, without being owner the database.

In time, sorry about my english, Im still studying.

Answer 4

If you add uses to db_ddladmin they can issue any DDL in the database, including creating DDL triggers.

If you want a user to only be able to meddle with one single table, you need to grant permissions for that table only.

Answer 5

Thanks for all the answers. Sorry for the response time. I was traveling, but I'm very grateful. I'm going to use a specific rule as directed by you. Thank you very much.

Thanks Lukasz, Erland and all contributors