1

The db_ddladmin is one of the powerful role. It allows user to create, drop, or modify any objects within a database, regardless of who owns it. (https://www.mssqltips.com/sqlservertip/1900/understanding-sql-server-fixed-database-roles/).

Question1, It states it allows a user to create, drop, or modify 'any objects' within a database, does this include 'system objects' or just 'user objects' within the database? I am trying to find out if someone in this role would be able to drop system tables, system views, etc. within the database.

Question2, Is there any script/code available that would list all of the permissions of a fixed database role (like db_ddladmin)?

I am working on SQL Server Versions 2012 through 2019.

Thanks in advance.

2 Answers 2

0

It states it allows a user to create, drop, or modify 'any objects' within a database, does this include 'system objects' or just 'user objects'

System objects can't be modified directly by any users.

Is there any script/code available that would list all of the permissions of a fixed database role (like db_ddladmin)?

No. Fixed database server roles predate the more modern permissions that can be granted directly, and are "baked in" to the engine. So you won't see individual permissions grants like you will for a user or a user-defined role. eg this returns no rows:

select *
from sys.database_principals dp
join sys.database_permissions p 
  on dp.principal_id = p.grantee_principal_id
where dp.name = 'db_ddladmin'
1
  • Thanks David for the response I was looking for "System objects can't be modified directly by any users." I also spoke with a Microsoft Support Engineer and he responded with the following: "Any user including the ones with sysadmin server role cannot drop or alter any system objects. SQL Server has built in mechanism to prevent such operations. Also any attempts to update any of the system tables is also not allowed and such operations result in the following error: Ad hoc updates to system catalogs are not allowed. Dec 23, 2022 at 0:41
0

db_ddladmin has the following individual permissions: ALTER ANY ASSEMBLY, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY DATABASE DDL TRIGGER, ALTER ANY DATABASE EVENT, NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMETRIC KEY, CHECKPOINT, CREATE AGGREGATE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE QUEUE, CREATE RULE, CREATE SYNONYM, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE XML SCHEMA COLLECTION, REFERENCES (these are also visible on the Permissions Poster: https://aka.ms/sql-permissions-poster)

The only script I know that will show these kinds of permissions is sp_help_permissions by Daniel Hutmacher. (https://sqlsunday.com/2017/09/27/effective-permissions/)

EXECUTE dbo.sp_help_permissions @principal='db_ddladmin';

Be careful granting db_ddladmin... because it has CREATE DATABASE TRIGGER it has the ability to elevate privileges... especially when the database is called by a maintenance SQL Agent job running as a SYSADMIN.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.