RE: Keeping User From Deleting Table Records

Linchi - Thanks. Yes, I'm aware that I will have to remove "sa" SQL
authentication and authenticate with Windows. As you may have noticed I did
try that to some degree with my test user - but you also say "... grant only
read permissions to that user." Is this Windows "read" permission setup via
Windows or is this SQL-assigned read permission done through SQL Server?

"Linchi Shea" wrote:

If the user is connecting to the SQL instace as sa, the very first thing to
do is to remove sa and have him connecting with either a Windows account or
SQL login, and grant only read permissions to that user. To allow him to
select from any table in a database, you can put his user account in the
db_datareader role in that database.


"meangene" wrote:

Using MSSQL Server 2000, SP4. My supv uses Access 2003 to run queries off
tables in SQL and connects via ODBC using "sa". If he only needed access to a
few specific tables then I would create views for him; however, he says he
needs access to virtually all tables. Recently he inadventantly deleted many
records out of a table via Access. What security can I set up in SQL 2000
that will allow him to run queries via Access but keep him from deleting
records. I have tested with a test Windows account (network OS is Win2003
Server, R2) with odbc set to windows logon and in SQL added the user to
Security/Logins with no server role and added them as a User in the db with
db_denydatawriter selected and even went so far as to click on Permission and
put the big red X in delete column. However, when I login to domain with test
user account and use Access to run a query, I can still delete records.