The db_ddladmin is another powerful role because it allows a 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 a script/code available that would list all of the permissions of a fixed database role (like db_ddladmin)?
Thanks in advance.
November 24, 2022 at 5:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
The documentation shows what permissions are granted: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver16
As for changing system objects - no.
From the above documentation sp_dbfixedrolepermission displays permissions of a fixed database role.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 23, 2022 at 12:57 am
Jeffery, thanks for the direct answer that I was looking for "As for changing system objects - no."
I later spoke with a Microsoft Support Manager, and he provided 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."
December 29, 2022 at 4:42 pm
I later spoke with a Microsoft Support Manager, and he provided 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."
Looks perfect, if it is allowed , it might have adverse effect on SQL working.
Regards
Durai Nagarajan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply