msdb Public Role Permission Query

Question

Why does the msdb public role have execute permissions to all sp_sysdac stored procedures?

Answer 1

I can't see any good reason.. Then again, I have no idea what these procedures are intended for.

Answer 2

Hi @Saransvan ,

From below illustrator, you can see the permission of public role:

SQL Server has many database objects such as table, view, stored procedure, function, constraints, rule, Synonym, triggers. Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Please refer to Database-Level Roles to get more information. And the picture is not very clear, you can download the attachment(PDF) if you needed.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

73651-microsoft-sql-server-2017-and-azure-sql-database-p.pdf

Answer 3

Hi Carrin,

Your answer does not match the question! The question is not how authorizations are managed on which objects in SQL Server, but WHY there are authorizations for special objects, although they do not seem to make sense.

Answer 4

Thank you for clarifying UweRicken-6497 ! Exactly - my question is why only these SPs . It's hard to find much information about them, but as you said, they are related to data tier functions, DACPACs etc. I need to know why public needs access, concerned that they are a potential security hole.

Answer 5

The link shared by Uwe gives a little more information. I don't think that there is a big security hole here, since the procedure only seems to play with their own tables (but I did not read the code for all of them). They also seem to be doing their own security checks. It seems that you have to be member of the server role dbcreator or have the permission CREATE ANY DATABASE to add a DAC instance.

If no one on the server uses Data-Tier Application, I guess you can revoke the permission on them.

I don't use DACPAC much myself, so I can't say whether this is something useful.