Re: Keeping User From Deleting Table Records

Hugo - Thanks and will take a look at that but may be a day or two - I'm
busier than a one-legged man in a ***-kicking contest at the moment!

"Hugo Kornelis" wrote:

On Mon, 14 Sep 2009 13:56:01 -0700, meangene wrote:

Still no joy in setting SQL permissions for a user.
- I have created a new Windows user login in Security/Logins that is
domain\user
(mydoman\user1) for a valid domain user account in AD. I have given this
user no Server Role and access to the db at issue.
- In Users in the db at issue I have created a new user by selecting
mydoman\user1 from the Login dropdown list. By default it is in db role
Public but I also check db_datareader and db_denydatawriter.
- For a table (CustomerMaster) under All Tasks\Manage Permissions I have a
red "X" for mydomain\user1 in the Delete column.
- I login to the network as domain/user and create an odbc system dsn entry
to the sql server "With Windows NT authentication..." and check "Connect to
SQL Server to obtain default settings for the additional configuration
options." (user1 is greyed-out in Login ID). I also select "Change the
default database to:" to the db at issue.
- I create a new Access db using system dsn above and link to the
CustomerMaster table. I then create a select query that grabs customernumber
and name. I highlight first record and click "delete' and am able to delete
the record.
What could I be setting up wrong?

Hi Meangene,

Unfortunately, I have removed SQL Server 2000 from my system quite a
while ago, and the tools have changed a lot in the meantime. I am unable
to check if Enterprise Manager did indeed use a red "X" for denying
permissions.

One obvious thing that might have gone wrong is the permissions of the
Windows domain user account. Could you double-check that this is a
"normal" account, i.e. it is not members of the Windows group
BUILTIN\Admnistrators or the local administrator's group. If that is the
case, then the login will automatically be member of the sysadmin fixed
server role, and hence have supreme powers on the server.

Let me know if that's not it. In that case, I know someone who probably
still has a copy of SQL2000 running on a virtual machine and I'll try to
interest him for this thread.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

.

Quantcast