Hi,
how to set read only permission to whole MS SQL 2005 database for some user?
How to set read only permission to some tables in this database only and
other tables in this database are denied? It is for some user again?
thank you
Ales
Denny Cherry - 11 Jul 2008 21:18 GMT
You can grant users read rights to all objects in the database by
making the user a member of the db_datareader database role.
If you want to grant rights to only some tables you need to grant the
rights to those objects directly. This is gone with the GRANT
command.
GRANT SELECT ON YourTable to UserName
If a user doesn't have rights to a table they will get Access denied.
If you need to override a granting of rights you can do with an
explicit DENY command.
DENY SELECT ON YourTable to UserName
A DENY will always override a GRANT unless rights are granted though a
fixed database role or fixed server role.
Denny
>Hi,
>how to set read only permission to whole MS SQL 2005 database for some user?
[quoted text clipped - 4 lines]
>
>Ales
Uri Dimant - 13 Jul 2008 08:14 GMT
Ales
That is why MS introdecd SCHEMA in SQL Server 2005.Actually it is very huge
topic and I'd suggest you to spend a few days to study it.
You can separate tables withing a different schema and grant an appropriate
permissions to whole schema
> Hi,
> how to set read only permission to whole MS SQL 2005 database for some
[quoted text clipped - 5 lines]
>
> Ales
Dan Guzman - 13 Jul 2008 13:32 GMT
> how to set read only permission to whole MS SQL 2005 database for some
> user?
You can add users to the db_datareader fixed database role to allow select
from all tables and views in the database. Alternatively, you can grant
permissions at the schema level: GRANT SELECT ON SCHEMA::dbo TO SomeUser.
> How to set read only permission to some tables in this database only and
> other tables in this database are denied? It is for some user again?
If you need to be selective, I suggest you create your own role and grant
permissions only to the desired tables. Although, you could use DENY to
negate a previous GRANT, I think DENY makes security more difficult to
manage.

Signature
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
> Hi,
> how to set read only permission to whole MS SQL 2005 database for some
[quoted text clipped - 5 lines]
>
> Ales