Why public is not mentioned as a fixed role in system database in SQL Server?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
5
down vote

favorite

While running

select * from sys.server_principals

for public role, in the column is_fixed_role is shown 0

enter image description here

But the documentation states that it is a fixed server role.
enter image description here

Documentation Link

Although it is stated that public is a little bit different from other roles because we can assign permissions to it. Anyway it is mentioned as a fixed role.

Can anyone explain this dilemma?

edited Sep 14 at 6:47

asked Aug 31 at 14:04

Eleonora Grigoryan

31311

2

Just for clarity, sys.database_principals shows database level things like users, so when checking server level things like logins, you should check sys.server_principals. That won't help here though, because public is also marked as non-fixed there. I'm not sure that it matters - you can't really do anything with the public role whether it's truly fixed or not (e.g. I'm not sure if the docs are wrong or the view is, but I would just post a question on the documentation).
â€“Â Aaron Bertrandâ™¦
Aug 31 at 14:11

Ok, thanks. I'll post there too
â€“Â Eleonora Grigoryan
Aug 31 at 14:14

add a commentÂ |Â

up vote
5
down vote

favorite

While running

select * from sys.server_principals

for public role, in the column is_fixed_role is shown 0

enter image description here

But the documentation states that it is a fixed server role.
enter image description here

Documentation Link

Although it is stated that public is a little bit different from other roles because we can assign permissions to it. Anyway it is mentioned as a fixed role.

Can anyone explain this dilemma?

edited Sep 14 at 6:47

asked Aug 31 at 14:04

Eleonora Grigoryan

31311

2

Just for clarity, sys.database_principals shows database level things like users, so when checking server level things like logins, you should check sys.server_principals. That won't help here though, because public is also marked as non-fixed there. I'm not sure that it matters - you can't really do anything with the public role whether it's truly fixed or not (e.g. I'm not sure if the docs are wrong or the view is, but I would just post a question on the documentation).
â€“Â Aaron Bertrandâ™¦
Aug 31 at 14:11

Ok, thanks. I'll post there too
â€“Â Eleonora Grigoryan
Aug 31 at 14:14

add a commentÂ |Â

up vote
5
down vote

favorite

While running

select * from sys.server_principals

for public role, in the column is_fixed_role is shown 0

enter image description here

But the documentation states that it is a fixed server role.
enter image description here

Documentation Link

Although it is stated that public is a little bit different from other roles because we can assign permissions to it. Anyway it is mentioned as a fixed role.

Can anyone explain this dilemma?

edited Sep 14 at 6:47

asked Aug 31 at 14:04

Eleonora Grigoryan

31311

While running

select * from sys.server_principals

for public role, in the column is_fixed_role is shown 0

enter image description here

But the documentation states that it is a fixed server role.
enter image description here

Documentation Link

Although it is stated that public is a little bit different from other roles because we can assign permissions to it. Anyway it is mentioned as a fixed role.

Can anyone explain this dilemma?

sql-server role system-tables

edited Sep 14 at 6:47

asked Aug 31 at 14:04

Eleonora Grigoryan

31311

edited Sep 14 at 6:47

asked Aug 31 at 14:04

Eleonora Grigoryan

31311

edited Sep 14 at 6:47

asked Aug 31 at 14:04

Eleonora Grigoryan

31311

asked Aug 31 at 14:04

Eleonora Grigoryan

31311

asked Aug 31 at 14:04

Eleonora Grigoryan

31311

2

Just for clarity, sys.database_principals shows database level things like users, so when checking server level things like logins, you should check sys.server_principals. That won't help here though, because public is also marked as non-fixed there. I'm not sure that it matters - you can't really do anything with the public role whether it's truly fixed or not (e.g. I'm not sure if the docs are wrong or the view is, but I would just post a question on the documentation).
â€“Â Aaron Bertrandâ™¦
Aug 31 at 14:11

Ok, thanks. I'll post there too
â€“Â Eleonora Grigoryan
Aug 31 at 14:14

add a commentÂ |Â

2

Just for clarity, sys.database_principals shows database level things like users, so when checking server level things like logins, you should check sys.server_principals. That won't help here though, because public is also marked as non-fixed there. I'm not sure that it matters - you can't really do anything with the public role whether it's truly fixed or not (e.g. I'm not sure if the docs are wrong or the view is, but I would just post a question on the documentation).
â€“Â Aaron Bertrandâ™¦
Aug 31 at 14:11

Ok, thanks. I'll post there too
â€“Â Eleonora Grigoryan
Aug 31 at 14:14

Just for clarity, sys.database_principals shows database level things like users, so when checking server level things like logins, you should check sys.server_principals. That won't help here though, because public is also marked as non-fixed there. I'm not sure that it matters - you can't really do anything with the public role whether it's truly fixed or not (e.g. I'm not sure if the docs are wrong or the view is, but I would just post a question on the documentation).
â€“Â Aaron Bertrandâ™¦
Aug 31 at 14:11

Ok, thanks. I'll post there too
â€“Â Eleonora Grigoryan
Aug 31 at 14:14

add a commentÂ |Â

2 Answers
2

active

oldest

votes

up vote
5
down vote

accepted

One reason that I can think of why public is NOT a fixed role - is_fixed_role is that the permissions can be changed.

From BOL:

The permissions that are granted to the fixed server roles (except public) cannot be changed.

Only assign public permissions on any object when you want the object to be available to all users.

Note: public is implemented differently than other roles, and permissions can be granted, denied, or revoked from the public fixed server roles.

the other fixed server roles permissions cannot be changed.

answered Aug 31 at 14:26

Kin

51.3k478181

add a commentÂ |Â

up vote
7
down vote

Public is just an odd duck.

But to apply some retroactive continuity to the documentation you might say that all these built in roles are "fixed" or unchangeable in ways that normal roles are not. The roles marked as is_fixed_role have fixed permissions and changeable membership. Public has fixed membership (everyone) and changeable permissions.

The column in sys.server_principals and sys.database_principals should be documented to clarify this.

answered Aug 31 at 14:26

David Browne - Microsoft

8,434622

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f216417%2fwhy-public-is-not-mentioned-as-a-fixed-role-in-system-database-in-sql-server%23new-answer', 'question_page');

);

Post as a guest

Name

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
5
down vote

accepted

One reason that I can think of why public is NOT a fixed role - is_fixed_role is that the permissions can be changed.

From BOL:

The permissions that are granted to the fixed server roles (except public) cannot be changed.

Only assign public permissions on any object when you want the object to be available to all users.

Note: public is implemented differently than other roles, and permissions can be granted, denied, or revoked from the public fixed server roles.

the other fixed server roles permissions cannot be changed.

answered Aug 31 at 14:26

Kin

51.3k478181

add a commentÂ |Â

up vote
5
down vote

accepted

One reason that I can think of why public is NOT a fixed role - is_fixed_role is that the permissions can be changed.

From BOL:

The permissions that are granted to the fixed server roles (except public) cannot be changed.

Only assign public permissions on any object when you want the object to be available to all users.

Note: public is implemented differently than other roles, and permissions can be granted, denied, or revoked from the public fixed server roles.

the other fixed server roles permissions cannot be changed.

answered Aug 31 at 14:26

Kin

51.3k478181

add a commentÂ |Â

up vote
5
down vote

accepted

One reason that I can think of why public is NOT a fixed role - is_fixed_role is that the permissions can be changed.

From BOL:

The permissions that are granted to the fixed server roles (except public) cannot be changed.

Only assign public permissions on any object when you want the object to be available to all users.

Note: public is implemented differently than other roles, and permissions can be granted, denied, or revoked from the public fixed server roles.

the other fixed server roles permissions cannot be changed.

answered Aug 31 at 14:26

Kin

51.3k478181

One reason that I can think of why public is NOT a fixed role - is_fixed_role is that the permissions can be changed.

From BOL:

The permissions that are granted to the fixed server roles (except public) cannot be changed.

Only assign public permissions on any object when you want the object to be available to all users.

Note: public is implemented differently than other roles, and permissions can be granted, denied, or revoked from the public fixed server roles.

the other fixed server roles permissions cannot be changed.

answered Aug 31 at 14:26

Kin

51.3k478181

answered Aug 31 at 14:26

Kin

51.3k478181

answered Aug 31 at 14:26

Kin

51.3k478181

answered Aug 31 at 14:26

Kin

51.3k478181

add a commentÂ |Â

up vote
7
down vote

Public is just an odd duck.

The column in sys.server_principals and sys.database_principals should be documented to clarify this.

answered Aug 31 at 14:26

David Browne - Microsoft

8,434622

add a commentÂ |Â

up vote
7
down vote

Public is just an odd duck.

The column in sys.server_principals and sys.database_principals should be documented to clarify this.

answered Aug 31 at 14:26

David Browne - Microsoft

8,434622

add a commentÂ |Â

up vote
7
down vote

Public is just an odd duck.

The column in sys.server_principals and sys.database_principals should be documented to clarify this.

answered Aug 31 at 14:26

David Browne - Microsoft

8,434622

Public is just an odd duck.

The column in sys.server_principals and sys.database_principals should be documented to clarify this.

answered Aug 31 at 14:26

David Browne - Microsoft

8,434622

answered Aug 31 at 14:26

David Browne - Microsoft

8,434622

answered Aug 31 at 14:26

David Browne - Microsoft

8,434622

answered Aug 31 at 14:26

David Browne - Microsoft

8,434622

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

搜尋此網誌

mjhjmtu