What exactly can user do with public role

Question

What exactly can user do with public role

Denise McMillan

SSCertifiable

Points: 6216
More actions
August 28, 2008 at 2:10 pm

#211905

I'm having trouble understanding the public role. I thought it could only view the databases. But it seems to also be able to update.
Can someone explain this, or direct me to documentation?
How can I see all the permissions that public has? Where does it say that public can edit data? Thanks very much.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Depends on what permissions have been granted to the public role. If no permissions have been granted to it, then users with just public will have no rights to do anything in the DB.

I'd guess, from what you're said, that someone's GRANTED update rights on one or more tables to public and that's what you're seeing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 2

Thank you, that's what I thought. But our vendor is trying to say that all rights to all databases come with public role. They must have granted these permissions. Do you have to go table by table to tell what permission is granted to public? I'm using Studio Manager. Or is there a command that will tell everything that is granted to public and what comes by default?

Thanks again for your help.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Denise McMillan (8/28/2008)
Thank you, that's what I thought. But our vendor is trying to say that all rights to all databases come with public role.

Tell then to stop talking garbage. 😀

They must have granted these permissions. Do you have to go table by table to tell what permission is granted to public? I'm using Studio Manager. Or is there a command that will tell everything that is granted to public and what comes by default?
Thanks again for your help.

exec sp_helprotect @username = 'public'

It should return just select on the system views and nothing else.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

For a quick - well not so quick - use SSMS object explorer

1. Select the database and expand it

2. In the resulting tree view expand security

3. The expand roles

4. Right click on Public

5. In the drop down menu select Properties

6. In the new window's left pane select Securables

Now here comes the time consuming aspect

7. In the top pane is shown all object in the database scroll down the list and select one of these

8. In the lower pane the explicit permissions will be shown

It will give you an idea of what the vendor has explicitly allowed for the public role.

Gail Shaw method will give you results in seconds my suggestion could take you days.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 5

Thanks for all your responses. I thought I was really missing something! I feel much better now.:)