RE: Keeping User From Deleting Table Records



OK, got it. Thanks!

"Linchi Shea" wrote:

SQL Server permissions inside a database. You don't need to deny DELETE. For
a regular user, if you don't grant DELETE permission, he cannot delete.

Linchi

"meangene" wrote:

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.

Linchi

"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: Keeping User From Deleting Table Records
    ... a regular user, if you don't grant DELETE permission, he cannot delete. ... authentication and authenticate with Windows. ... Windows or is this SQL-assigned read permission done through SQL ... user account and use Access to run a query, ...
    (microsoft.public.sqlserver.security)
  • Re: Keeping User From Deleting Table Records
    ... authentication and authenticate with Windows. ... Is this Windows "read" permission ... Windows or is this SQL-assigned read permission done through SQL ... select from any table in a database, you can put his user account ...
    (microsoft.public.sqlserver.security)
  • Re: Keeping User From Deleting Table Records
    ... authentication and authenticate with Windows. ... Is this Windows "read" permission ... Windows or is this SQL-assigned read permission done through SQL ... SQL login, and grant only read permissions to that user. ...
    (microsoft.public.sqlserver.security)
  • Re: Keeping User From Deleting Table Records
    ... a regular user, if you don't grant DELETE permission, he cannot delete. ... authentication and authenticate with Windows. ... Windows or is this SQL-assigned read permission done through SQL ... user account and use Access to run a query, ...
    (microsoft.public.sqlserver.security)
  • Re: Complete Neophyte Question(s)
    ... You should not have to go Google to find out what WITH GRANT means. ... Server comes with online documentation on you hard disk. ... WITH GRANT is one of the more esotheric features in SQL Server in my ... Or does that depend on the permission? ...
    (microsoft.public.sqlserver.security)