Can it be read only ?

We get connected to a SQL Server 2000 database by using
Access 2000 with ODBC Connection.
Is it possible for us to read data from the SQL Server
Database with Access only ? This is because we find that
we can update the data in SQL Server with Access BUT we
don't want to make any changes accidentially.
Hi
You can make the whole database read only but then no-one will be able to
update any data. By only granting select permissions to the tables for the
user you are connecting with, they will not be able to update/insert data if
they access the data directly (SELECT statements). If you are using stored
procedures then it will be that users' permissions which will restrict the
actions and the user that you connect with will need execute permissions on
the stored procedure.
Books online is a great resource for finding out things related to SQL
Server, check out the topics "Setting Database Options", "Managing
Permissions" and "Owners and Permissions". If Books online is not in your SQL
Server program group on the start menu, it can be downloaded from
http://www.microsoft.com/sql/techinf...000/books.mspx
John
"Jason" wrote:

> We get connected to a SQL Server 2000 database by using
> Access 2000 with ODBC Connection.
> Is it possible for us to read data from the SQL Server
> Database with Access only ? This is because we find that
> we can update the data in SQL Server with Access BUT we
> don't want to make any changes accidentially.
>
|||Dear John,
From your reply, my understanding is that the behaviour depends on the user
account used to create the ODBC connection.
If I create a login that belongs to db_datareader and public database roles
AND use this account for ODBC connection, I will be able to SELECT from
tables and cannot alter the data.
On the other hand, is there any database role that allows a login to execute
Stored Procedures (Instead of setting up one by one) ?
Thanks
Jason
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6EFF1E1A-C551-49E1-806B-6B1C9A22C13A@.microsoft.com...[vbcol=seagreen]
> Hi
> You can make the whole database read only but then no-one will be able to
> update any data. By only granting select permissions to the tables for the
> user you are connecting with, they will not be able to update/insert data
> if
> they access the data directly (SELECT statements). If you are using stored
> procedures then it will be that users' permissions which will restrict the
> actions and the user that you connect with will need execute permissions
> on
> the stored procedure.
> Books online is a great resource for finding out things related to SQL
> Server, check out the topics "Setting Database Options", "Managing
> Permissions" and "Owners and Permissions". If Books online is not in your
> SQL
> Server program group on the start menu, it can be downloaded from
> http://www.microsoft.com/sql/techinf...000/books.mspx
> John
>
> "Jason" wrote:
|||Hi
Adding users to the db_datareader will mean they can select all data from
any user table in the database, this may not be restictive enough if you have
say configuration or sensitive information that you don't want them to see.
The best way to assign specific permissions would be to create a role
(sp_addrole) and assign the permissions to that role, then make an NT Group
or SQL Server user a member of the role (sp_addrolemember). The users can
then be added to the appropriate group rather than having to assign specific
permissions to each one.
John
"Jason" wrote:

> Dear John,
> From your reply, my understanding is that the behaviour depends on the user
> account used to create the ODBC connection.
> If I create a login that belongs to db_datareader and public database roles
> AND use this account for ODBC connection, I will be able to SELECT from
> tables and cannot alter the data.
> On the other hand, is there any database role that allows a login to execute
> Stored Procedures (Instead of setting up one by one) ?
> Thanks
> Jason
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6EFF1E1A-C551-49E1-806B-6B1C9A22C13A@.microsoft.com...
>
>
|||Dear John,
From my understanding, you suggest to create a application role that assign
permissions to tables / stored procedures.
Then create logins and add them to the role created before.
Jason
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F2EB7ECA-7D62-467F-8E65-F9D255499776@.microsoft.com...[vbcol=seagreen]
> Hi
> Adding users to the db_datareader will mean they can select all data from
> any user table in the database, this may not be restictive enough if you
> have
> say configuration or sensitive information that you don't want them to
> see.
> The best way to assign specific permissions would be to create a role
> (sp_addrole) and assign the permissions to that role, then make an NT
> Group
> or SQL Server user a member of the role (sp_addrolemember). The users can
> then be added to the appropriate group rather than having to assign
> specific
> permissions to each one.
> John
> "Jason" wrote:
|||Hi Jason
Just a "normal" SQL Server role would probably be better as you wont need to
worry about a setting the application role and the password. If you are using
windows authentication create a group and assign the NT logins for the users
that require access to that group. Then add the NT group as a member of the
SQL Server role (sp_addrolemember). If you are using SQL Server
authentication add the each SQL Server user to the SQL Server role.
Only allowing the users to access the data through Stored Procedure would
enable you to create a more restricted policy.
John
"Jason" wrote:

> Dear John,
> From my understanding, you suggest to create a application role that assign
> permissions to tables / stored procedures.
> Then create logins and add them to the role created before.
> Jason
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:F2EB7ECA-7D62-467F-8E65-F9D255499776@.microsoft.com...
>
>