msdb Public Role Permission Query

Saransvan 106 Reputation points
2021-03-02T15:25:09.427+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,778 questions
0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.4K Reputation points MVP
    2021-03-02T22:59:29.987+00:00

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

  2. CarrinWu-MSFT 6,856 Reputation points
    2021-03-03T05:58:46.007+00:00

    Hi @Saransvan ,

    From below illustrator, you can see the permission of public role:
    73550-public-role.png
    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

    0 comments
  3. Uwe Ricken 1 Reputation point MVP
    2021-03-03T06:29:18.687+00:00

    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.

    0 comments
  4. Saransvan 106 Reputation points
    2021-03-03T08:49:19.34+00:00

    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.

  5. Erland Sommarskog 101.4K Reputation points MVP
    2021-03-03T19:35:59.173+00:00

    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.

    0 comments