Does the db_ddladmin fixed database role permissions apply to db system object?

Question

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.

Answer 1

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'

Answer 2

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.