RE: Keeping User From Deleting Table Records

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.

Relevant Pages

  • Re: SQL Connection Error
    ... then your sql connection string can stay as you had it. ... However, then, each person connecting to your database will need a Windows Account on the SQL Server and associated to the database. ...
  • Integrated Security logon to SQL Server
    ... amongst other things connect to a SQL database. ... SQL username/password) or in "integrated" mode (where the windows account ... the database access code into a COM+ package and run that under an account ...
  • Specifying the Windows Account in merge replication setup
    ... having trouble with the merge agent security... ... I am selecting to Run under the Windows Account but I'm not sure how to ... enter the SQL Express process account. ...
  • RE: Active Directory ports required by SharePoint
    ... \par SPS can use an SQL account to access the remote database instead of windows account, if the SQL server support SQL authentication. ... \par If windows authentication is required, we have to set the two domain trusted each other so that we can user domain B user account in domain A. And here is required ports for this solution: ...
  • Re: Access the filesystem with SQL server 2005
    ... statement as a SQLuser and it works. ... my windows account and is stops working. ... Program Manager - SQL Server ...