RE: Keeping User From Deleting Table Records

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: Login failed for user (null)
    ... Not associated with a trusted SQL Server connection. ... However, if you deal with the Domain controller, most of the controls there ... > (Windows 2003 server, domain member). ...
    (microsoft.public.inetserver.asp.db)
  • Re: Login failed for user (null)
    ... Not associated with a trusted SQL Server connection. ... However, if you deal with the Domain controller, most of the controls there ... > (Windows 2003 server, domain member). ...
    (microsoft.public.sqlserver.connect)
  • Re: Login failed for user (null)
    ... Not associated with a trusted SQL Server connection. ... However, if you deal with the Domain controller, most of the controls there ... > (Windows 2003 server, domain member). ...
    (microsoft.public.sqlserver.security)
Loading