Remove "public" group?

[jenlion]

I have a customer that wants to remove the public group from their SQL server.

My instinct is to leave it alone, but I can't say *why*. Anybody know a good reason to keep it? Or is it fine to delete it?

 

[mrdenny]

You can't delete the built in database roles.

Public is required as all users are a member of the public role. It's used to grant the users there basic access into the database including access to the system tables.

What's the reasoning for deleting the public role?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jenlion]

Denny, thanks.

I'd love to say what I think about the reason, but there's a very real chance the same people will be browsing this forum looking for the same answer! I've referred them to this site for answers in the past... my luck is that this time they will actually go looking up an answer!

At any rate, they are extremely security-minded, so much so that on the most crucial day of a new software install they cut off my access to the database. I got it back, but that's never happened before with any of the other customers that we work with. Maybe somebody up there has been badly burned in the past... all I know is that they run an extremely strict environment that is very difficult to work with.

I'll pass on "Sorry, you *can't* delete public". I didn't realize it was impossible to do it; I'll have to run it through and see what it tells me!

Thanks!

 

[Catadmin]

If they are working on SQL Server 2005, the public role is much more secure than it was on earlier versions of SQL. If they are working on SQL Server 2000, tell them to upgrade.

I have a feeling their "burned" issue has to deal with SQL 2k's tendancy to grant everyone the ability to see stuff in the object browser of EM if they have login ability to the database (because of the public role), but not any other permissions. This doesn't happen anymore with SQL 2k5.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[jenlion]

It is SQL Server 2000, we are stuck with that until the software we are running is updated in a few months (we hope) to handle 2005.

Actually they have never worked with SQL before -- or Windows, really. They were a linux/unix shop for a long time. Any burning must have taken place long before this.

Thanks!

 

[mrdenny]

If they don't like the ability of everyone who logs into the database with QA or EM to be able to view all the objects they can revoke the select rights to the sysobjects table to public there by preventing anyone but members of the fixed database roles, members of the sysadmin fixed server roles and anyone who has been granted direct access to the sysobjects table to view the list of objects in the database.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com