Assign a user defined role to PUBLIC

[j2willi4]

is it possible to assign a user defined role to the PUBLIC role? and if so, is there a simple way to check that?


I have seen this in oracle and informix but cant find anything on it with SQL server.

 

[mrdenny]

The public role has very limited rights. No additional rights should be granted to public as granting rights to public grants those rights to everyone who can access the database (and the server if your guest account is enabled).

Yes a role can belong to other roles, however you can't modify the public role (or at least you shouldn't).

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

 

[j2willi4]

I am not concerned with 'should' or 'should not.'

I need to figure out what it 'can' do. If 3rd party apps and DBAs didn't grant additional privs to PUBLIC, my job would be a lot simpler

I know that in Oracle UserRole1 can be assigned to PUBLIC so that every user has the privileges of UserRole1 through virtue of PUBLIC. Is this the same with SQL server?

according to MS books-online: "PUBLIC - Cannot have users, groups, or roles assigned to it because they belong to the role by default."

But that seems to be looking at it from the reverse perspective. (assigning PUBLIC to UserRole1)

Can anyone clarify?

 

[mrdenny]

Yes you can assign the public role to another role which would then grant all users of the database rights to that other 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

 

[j2willi4]

thanks!!

 

[mrdenny]

no problem.

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