Re: Set column level permissions in Access adp

From: Michael (mhweiss@hotmail.com)
Date: 03/20/03 

From: "Michael" <mhweiss@hotmail.com>
Date: Thu, 20 Mar 2003 12:50:00 -0800

Thank you! That is exactly it as far as I can
determine...I didn't realize that you couldn't explicity
deny priveleges to the dbo...thanks again!
Michael
>-----Original Message-----
>Gotcha!!! So, you are logging into the database as a dbo
then :-) dbo is the
>owner of the database, and can do anything in the
database. You cannot deny
>permssions for dbo on a table. You should be using a non-
dbo user.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>
>
>
>"Michael" <mhweiss@hotmail.com> wrote in message
>news:1ec001c2ef18$f2201e80$a501280a@phx.gbl...
>Thank you for your suggestions! I did what you suggested
>and created the log table and trigger. The adp is using my
>login name and the user name is dbo. Even denying update
>permission on the column in question to the dbo user does
>not seem to have any effect. For the adp, QA, and in EM, I
>use windows authentication and that is how the server is
>registered. I am sure it is something I am overlooking or
>something I am doing incorrectly but this sure has me
>befuddled! Any other thoughts come to mind?
>Thank you again,
>Michael
>>-----Original Message-----
>>I have a strong feeling that the ADP is using some other
>login :-)
>>
>>The ADP is connecting using SQL Authentication or Windows
>Authentication?
>>Regarding EM, again, how did you register the server?
>Using SQL
>>authentication or Windows?
>>
>>For which exact user in the database are you denying
>UPDATE permissions?
>>
>>Can you create a trigger on the table in question, to log
>the login and user
>>names to a different tables and see what you get? For
>example, create a
>>temporary log table:
>>
>>CREATE TABLE MyLog (LName sysname, UName sysname)
>>GO
>>
>>Then create the following trigger on the table you are
>trying to update:
>>
>>CREATE TRIGGER YourTriggerName
>>ON YourTableName
>>FOR INSERT, UPDATE, DELETE
>>AS
>>BEGIN
>> INSERT INTO MyLog SELECT SUSER_SNAME(), USER_NAME()
>>END
>>GO
>>
>>Now, connect using your ADP and run your updates. Then,
>connect using Query
>>Analyzer and run the following:
>>
>>SELECT LName AS [LoginName], UName AS [UserName] FROM
>MyLog
>>
>>See if the login and usernames from the above output
>matches the user name
>>on which you configured the permissions.
>>--
>>HTH,
>>Vyas, MVP (SQL Server)
>>http://vyaskn.tripod.com/
>>
>>
>>
>>
>>"Michael" <mhweiss@hotmail.com> wrote in message
>>news:24fd01c2ef02$43ac17c0$3401280a@phx.gbl...
>>Okay, I tried granting update permissions on the table
>>itself and then denying update permission on the column
in
>>question. Still it seems to have no effect on entering,
>>changing, or deleting data in that column through the
adp.
>>Sooooo...I went in and denied updates on the entire table
>>and tried entering data into the column through the adp
>>and was able to do so without receiving any warnings at
>>all. Any idea what could be wrong given that I can seem
to
>>completely circumvent the security settings entirely via
>>the adp? Actually, it seems I can circumvent the settings
>>via EM as well...
>>Thank you!
>>Michael
>>>-----Original Message-----
>>>Here's what I get, when I try to update a column on
which
>>UPDATE permission
>>>is denied to me:
>>>
>>>UPDATE permission denied on column 'j' of
>>object 'SomeTable', database
>>>'tempdb', owner 'dbo'.
>>>
>>>The only other think I can think of is that, you denied
>>the UPDATE
>>>permission on the column first, and then granted the
>>UPDATE permission on
>>>that table. This would have reset the previous DENY. So,
>>set the permissions
>>>again and try again: First, grant UPDATE permission on
>>the table, then DENY
>>>UPDATE permission on that specific column, now try the
>>UPDATE from ADP.
>>>--
>>>HTH,
>>>Vyas, MVP (SQL Server)
>>>http://vyaskn.tripod.com/
>>>
>>>
>>>
>>>
>>>"Michael" <mhweiss@hotmail.com> wrote in message
>>>news:188301c2ee5c$b3fab870$a401280a@phx.gbl...
>>>I checked using sp_who and the login being used
currently
>>>by the adp is my own. I have explicity denied UPDATE
>>>priveleges on the column in question for my login so I
am
>>>perplexed as to why I can still go into the adp and
>update
>>>the column value. It seems like I should receive some
>type
>>>of warning message regarding insufficient priveleges for
>>>update on that column. Any other thoughts come to mind?
>>>Thank you,
>>>Michael
>>>>-----Original Message-----
>>>>I don't know much about ADPs, but do check your connect
>>>string, if there's
>>>>one.
>>>>
>>>>You can always find out the login name used by your ADP
>>>by running sp_who in
>>>>SQL Server. Look at the login name column of the
output.
>>>You could also
>>>>trace it using SQL Server Profiler.
>>>>--
>>>>HTH,
>>>>Vyas, MVP (SQL Server)
>>>>http://vyaskn.tripod.com/
>>>>
>>>>
>>>>
>>>>
>>>>"Michael" <mhweiss@hotmail.com> wrote in message
>>>>news:17e901c2ee55$8aa9a640$a501280a@phx.gbl...
>>>>How can I tell which login my adp is using? Any
>>>>suggestions would be welcome!
>>>>
>>>>
>>>>>-----Original Message-----
>>>>>Then your ADP is probably not using the same login,
>that
>>>>has been denied
>>>>>permissions on this column.
>>>>>--
>>>>>HTH,
>>>>>Vyas, MVP (SQL Server)
>>>>>http://vyaskn.tripod.com/
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>"Michael" <mhweiss@hotmail.com> wrote in message
>>>>>news:18d301c2ee4e$e544b420$a301280a@phx.gbl...
>>>>>I have an Access adp project (Access 2000 version)
that
>>>>>connects to a SQL Server 2000 DB using WinNT
>>>>>authentication. I need to lock down one column in a
>>table
>>>>>so that only one specific user can update data in that
>>>>>column. Setting user permissions on this column in EM
>>>does
>>>>>not seem to have any impact on a user's ability to
>>update
>>>>>or change values in that column via a form in the adp
>>>>>project. What am I missing here or doing incorrectly?
>>>>>Thank you,
>>>>>Michael
>>>>>
>>>>>
>>>>>.
>>>>>
>>>>
>>>>
>>>>.
>>>>
>>>
>>>
>>>.
>>>
>>
>>
>>.
>>
>
>
>.
>

Relevant Pages

  • Re: ADP: Error calling SP with "EXEC sp_helprotect" statement
    ... By owner of the database, I simply mean an account such as sa that will ... I've tried your first piece of code yesterday both with ADP and QA and both ... Security and dbo. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: ADP: Error calling SP with "EXEC sp_helprotect" statement
    ... before I don't know how to determine the owner of the database and the ... same applies to objects in the database. ... Security and dbo. ... I've got that right - we're both logging in to an Access ADP file as ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Access 2003 to SQL Server 2000 over a VPN
    ... sufficiently fast response times, ADP may be a good enough client, ... Replication can be another solution, ... you're careful about your database design, though, that may be the way to ... As a result of the difficulties I've had, I try to avoid SQL Server ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Question on conversion to ADP
    ... the money toward developping .NET technologies instead of Access. ... result of passthrough queries read/write instead of read only and also make ... database become corrupted when the users will start making their very own ... of ADP but what they are pushed toward is - how could I say that politely - ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Access 2003 to SQL Server 2000 over a VPN
    ... you're careful about your database design, though, that may be the way to ... As a result of the difficulties I've had, I try to avoid SQL Server ... Would you consider using an ADP talking across an fast broadbank link to ... A thin client interface has been suggested, ...
    (microsoft.public.access.adp.sqlserver)