question

Saransvan-0628 avatar image
0 Votes"
Saransvan-0628 asked ·

msdb Public Role Permission Query

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

sql-server-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered ·

Hi @Saransvan-0628,

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



·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

UweRicken-6497 avatar image
0 Votes"
UweRicken-6497 answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Saransvan-0628 avatar image
0 Votes"
Saransvan-0628 answered ·

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.

· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Saransvan-0628, may I ask why do you want to know msdb public role have execute permissions to all sp_sysdac stored procedures? Are the permissions bring a security issue for you?

0 Votes 0 ·

Hi yes, It's come up on a security review.

0 Votes 0 ·

Hi, public is implemented differently than other roles, and permissions can be granted, denied, or revoked from the public fixed server roles. As Erland said, it's possible to revoke the permissions

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.