PUBLC role access in SQL2k 1

[j2willi4]

Hello!

I am looking for a detailed explanation of the PUBLIC role within SQL server 2000.

Is it possible to have a PUBLIC role within a DB in SQL server, provide access to the users in that DB to another DB on that server?

Do any of the system tables or stored procedures the PUBLIC role has access to give those users any ability to view User DB data.

Thanks for any help you all can provide!

 

[ptheriault]

Public Role

The public role is a special database role to which every database
user belongs. The public role contain default access permissions
for any user who can access the database. This database role cannot
be dropped.

As a memeber of the Public role you do not have any access to user data.

 

[ptheriault]

By the way...I would never grant any permissions to the public role.
Here is a good link to explain the public role.

http://www.sqlservercentral.com/columnists/bkelley/sqlserversecurityfixeddatabaseroles.asp

 

[j2willi4]

Thanks for the quick reply...

"The public role contain default access permissions
for any user who can access the database."

It's those default access permissions i am really trying to document. Any help on that?

 

[ptheriault]

I'm not sure I could find what you are looking for but try this link. It should help.

http://www.databasejournal.com/features/mssql/article.php/1478701

Also this query will show you if the public role has any permissions right now. You should get back a 0.
Anything > than 0 means it has permissions some place.


select count(*) as PublicPermissionCount
from sysprotects P inner join sysusers U on P.Uid = U.UID
inner join sysobjects O on P.ID=O.ID
where P.uid=0 and o.Type<>'S'
and o.Name not like 'sys%'
and O.Name not like 'sync%'

 

[j2willi4]

Thanks ptheriault!

Anyone else have any additional insight?

 

[ptheriault]

I did find this for you.
The public role has select only permissions in every database on the following tables & views.
syscolumns syscomments sysconstraints sysdepends sysfilegroups sysfiles sysforeignkeys sysfulltextcatalogs
sysfulltextnotify sysindexkeys sysindexes sysmemebers
sysobjects syspermissions sysprojects sysreferences
syssegemtns systypes sysusers

 

[j2willi4]

Thanks again... how did you come up with this list? did you find it online, or run a query?