Public Role

Question

Public Role

SA-1

SSCrazy Eights

Points: 8113
More actions
September 29, 2006 at 7:52 am

#62817

Is there a way to disable the public role?
I'm trying to use a tool for auditing my databases and access granted via this role shows as a medium risk.
TIA.

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

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

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 1

[All of this applies to SQL 2000, and 7.0 too, I think. Don't know about 2005 yet...

You cannot disable or drop the public role. However, you can revoke (NOT deny!) any and all rights, privileges, and permissions granted to that role, rendering it irrelevant.

Any user of a given database will be a member of the public role, so the next thing to work over is the list of users. Be sure to drop any that don't require access to a database.

Any (SQL Instance) login that is not configured as a user in a database will be granted the same rights as the "guest" user--so, unless you want that, be sure to drop the guest user entry as well. (A fiddly point, the "guest" entry will persist in the database system tables, but it will be flagged as disabled.)

Philip

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 2

So revoking access to the public role would not break anything.

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 3

It depends upon how the database is being used. If you have applications or users currently using the database who are gaining their access rights via the permissions assigned to the public role, and you revoke those rights from the public role, then they will no longer be able to do what they have been doing. If this is what you want (locking down the system), good, otherwise you'll need to analyze and re-adjust your database security configuration.

Philip