Public Role in SQL Server 2000 SP3

If I add a user to a database with Public Database Role, I
would like to know what privilege has been granted to him.
This is because we find that he is not able to SELECT any
table.
The default permissions of the public role allow SELECT from sysobjects and
other meta-data objects.
A security best practice is to create your own roles and grant object
permissions to those roles as needed. You can then control security by user
role membership.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.
|||Hi,
The public role is a special database role to which every database user
belongs. Captures all default permissions for users in a database.
The user with public role can access only the tables / objects granted
permissions exclusevely to PUBLIC role.
For eg:
Hari is a database owner and he create a table customer. After creation he
gave the below statement to give previlage to customer table.
GRANT select on customer to public
IN this case user inside public role will be able to access the table
customer. Otherwise he can not.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.
|||If I want him to select all tables, what is the easiest
way to achieve it ?
Thanks

>--Original Message--
>The default permissions of the public role allow SELECT
from sysobjects and
>other meta-data objects.
>A security best practice is to create your own roles and
grant object
>permissions to those roles as needed. You can then
control security by user
>role membership.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
Role, I[vbcol=seagreen]
him.[vbcol=seagreen]
any
>
>.
>
|||HI,
You can assign the user "db_datareader" database fixed role. This will
enable the user to select from all tables
from the particular database.
How to add the role.
sp_addrolemember 'db_datareader','user_name'
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx.gbl...[vbcol=seagreen]
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
> from sysobjects and
> grant object
> control security by user
> message
> Role, I
> him.
> any
|||To add to Hari's response, db_datareader fixed database role members can
select from any table or view. However, execute permissions on read-only
stored procedures are not included.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx.gbl...[vbcol=seagreen]
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
> from sysobjects and
> grant object
> control security by user
> message
> Role, I
> him.
> any