Re: Keeping User From Deleting Table Records


Uri - Yeah, on prod db this would keep our business app from deleting records
when it needs to. Is there a way to make the trigger specific to user logins?

"Uri Dimant" wrote:

Another approach could be using a trigger (even you are 'sa') , however , i
would not implement such on prod machine
create table t (c int)

go

insert into t values (1)

go

create trigger my_tr on t

for delete

as

rollback

--usage

delete from t

select * from t

"meangene" <meangene@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B3D6CFD9-812E-43EA-8CC0-D484EA4479CD@xxxxxxxxxxxxxxxx
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
    ... 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
    ... 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: How do I get a working Outlook.fav file after reinstallation?
    ... the folder has permission for your old windows user account, ... need to add your user account to the list of accounts at the top and give it ... full permission then click advanced and take ownership on the owner tab. ... Teach Yourself Outlook 2003 in 24 Hours ...
    (microsoft.public.outlook.installation)
  • 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
    ... 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 Server? ...
    (microsoft.public.sqlserver.security)