How to prevent users from updating specific field from table on database level?

You could restrict the table to read only for most users, then have them execute the update as another user with write permission.

You might have to move the field in question to its own table.  Some RDBMS allow you to control updates at the column level but I don't know if this version of SQL Server is among them.

Why would users ever be given the rights to create updateable queries?  That is pretty scary.

@Dustin,

You could restrict the table to read only for most users

No, users need access to full table its just this one critical field that should not get changed.

@Pat,

You might have to move the field in question to its own table.

This sounds like a good plan, the problem is there is too much programming involved in order to implement that (like too many places is this being referenced, in fact almost entire FE would have to be revised).

Why would users ever be given the rights to create updateable queries?

Dont have much of a saying on this matter-:(

I was thinking to code something on the on update trigger that would cancel certain updates unless finds some value in another table, so I have the key to lock/unlock it...

What are you thoughts on that?

Thanks,
Ben

SQL Server supports column-level DENY. I'm pretty sure you can just DENY UPDATE for that column(s).

IIRC, SQL's actually had column-level security for quite some time, it's just that no one ever uses it.

IF you have SQL Server users and roles and security on the go, you can and should do as @ScottPletcher suggests.  I suspect you don't however.  What I would do in that case is build a new table in the backend with the same field.  Fill it with the existing data.

DON'T link it in the front-end.  Then the users can't see or muck with it.
Then you can build a trigger in SQL Server that anytime data in the column/table the users can see gets changed, the backend will look for a match in the hidden table.  If the ID/Value match then change will be permitted, if not the change will be rolled back.
https://msdn.microsoft.com/en-us/library/aa975750(VS.71).aspx

You build a form on an ADO recordset (no linked tables!) for UI to change the new backend table, and make that form one that only properly authorized folks can launch.  You then have them cut off at the knees.

But proper SQL server security is really the way to go.

@Scott,
Are you suggesting I create a user that has full rights and deny updates (for that column) to all other users?

@Pat,

You could use a trick to solve the problem easily.

Interestingly I used that trick already a few times and for some reason forgot about it now..

@Nick,
Your suggestion seems similar to mine (ID: 41709157), the main difference is with yours is meant to give some users permanent access for updates and then I stay with the same problem..(see below)

FYI- Some history
We had an Access database linked to SQL that was meant for users to create queries and own reports, at some point they realized the danger of users manipulating with data and requested to be read only, so I created a user in SQL with read only permission and had all tables linked thru DNS file belonging to that user.
Later on they decided some managers do need update rights to all tables, so I had two files linked to two SQL users.

Now with this problem looks like they realized this column should be restricted from managers as well.
This is why I am currently looking to be the only one changing it.

Thanks,
Ben

Ben,

WHO/WHAT MACHINE you allow to launch the update UI is up to you.
You don't even have to build it, if it will only be you doing the updates and your organization does not fear buses and heart attacks.  Do it all in SSMS if that floats the boat.

Me, I have this.  ReturnUserName is my friend.

Option Compare Database
Option Explicit

Private Type USER_INFO_2
    usri2_name As Long
    usri2_password  As Long  ' Null, only settable
    usri2_password_age  As Long
    usri2_priv  As Long
    usri2_home_dir  As Long
    usri2_comment  As Long
    usri2_flags  As Long
    usri2_script_path  As Long
    usri2_auth_flags  As Long
    usri2_full_name As Long
    usri2_usr_comment  As Long
    usri2_parms  As Long
    usri2_workstations  As Long
    usri2_last_logon  As Long
    usri2_last_logoff  As Long
    usri2_acct_expires  As Long
    usri2_max_storage  As Long
    usri2_units_per_week  As Long
    usri2_logon_hours  As Long
    usri2_bad_pw_count  As Long
    usri2_num_logons  As Long
    usri2_logon_server  As Long
    usri2_country_code  As Long
    usri2_code_page  As Long
End Type

Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long


Private Declare Function GetComputerName Lib "kernel32" _
Alias "GetComputerNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

 
Private Declare Function apiNetGetDCName _
    Lib "netapi32.dll" Alias "NetGetDCName" _
    (ByVal servername As Long, _
    ByVal DomainName As Long, _
    bufptr As Long) As Long
 
' function frees the memory that the NetApiBufferAllocate
' function allocates.
Private Declare Function apiNetAPIBufferFree _
    Lib "netapi32.dll" Alias "NetApiBufferFree" _
    (ByVal buffer As Long) _
    As Long
 
' Retrieves the length of the specified wide string.
Private Declare Function apilstrlenW _
    Lib "kernel32" Alias "lstrlenW" _
    (ByVal lpString As Long) _
    As Long
 
Private Declare Function apiNetUserGetInfo _
    Lib "netapi32.dll" Alias "NetUserGetInfo" _
    (servername As Any, _
    username As Any, _
    ByVal Level As Long, _
    bufptr As Long) As Long
 
' moves memory either forward or backward, aligned or unaligned,
' in 4-byte blocks, followed by any remaining bytes
Private Declare Sub sapiCopyMem _
    Lib "kernel32" Alias "RtlMoveMemory" _
    (Destination As Any, _
    Source As Any, _
    ByVal Length As Long)
 
Private Declare Function apiGetUserName Lib _
    "advapi32.dll" Alias "GetUserNameA" _
    (ByVal lpBuffer As String, _
    nSize As Long) _
    As Long
 
Private Const MAXCOMMENTSZ = 256
Private Const NERR_SUCCESS = 0
Private Const ERROR_MORE_DATA = 234&
Private Const MAX_CHUNK = 25
Private Const ERROR_SUCCESS = 0&


'******** Code Start ********
'This code was originally written by Dev Ashish.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
 
Function fGetFullNameOfLoggedUser(Optional strUserName As String) As String
'
' Returns the full name for a given UserID
'   NT/2000 only
' Omitting the strUserName argument will try and
' retrieve the full name for the currently logged on user
'
On Error GoTo ErrHandler
Dim pBuf As Long
Dim dwRec As Long
Dim pTmp As USER_INFO_2
Dim abytPDCName() As Byte
Dim abytUserName() As Byte
Dim lngRet As Long
Dim i As Long
 
    ' Unicode
    abytPDCName = fGetDCName() & vbNullChar
    If (Len(strUserName) = 0) Then strUserName = fGetUserName()
    abytUserName = strUserName & vbNullChar
 
    ' Level 2
    lngRet = apiNetUserGetInfo( _
                            abytPDCName(0), _
                            abytUserName(0), _
                            2, _
                            pBuf)
    If (lngRet = ERROR_SUCCESS) Then
        Call sapiCopyMem(pTmp, ByVal pBuf, Len(pTmp))
        fGetFullNameOfLoggedUser = fStrFromPtrW(pTmp.usri2_full_name)
    End If
 
    Call apiNetAPIBufferFree(pBuf)
ExitHere:
    Exit Function
ErrHandler:
    fGetFullNameOfLoggedUser = vbNullString
    Resume ExitHere
End Function
 
Private Function fGetUserName() As String
' Returns the network login name
Dim lngLen As Long, lngRet As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngRet = apiGetUserName(strUserName, lngLen)
    If lngRet Then
        fGetUserName = Left$(strUserName, lngLen - 1)
    End If
End Function
 
Function fGetDCName() As String
Dim pTmp As Long
Dim lngRet As Long
Dim abytBuf() As Byte
 
    lngRet = apiNetGetDCName(0, 0, pTmp)
    If lngRet = NERR_SUCCESS Then
        fGetDCName = fStrFromPtrW(pTmp)
    End If
    Call apiNetAPIBufferFree(pTmp)
End Function
 
Private Function fStrFromPtrW(pBuf As Long) As String
Dim lngLen As Long
Dim abytBuf() As Byte
 
    ' Get the length of the string at the memory location
    lngLen = apilstrlenW(pBuf) * 2
    ' if it's not a ZLS
    If lngLen Then
        ReDim abytBuf(lngLen)
        ' then copy the memory contents
        ' into a temp buffer
        Call sapiCopyMem( _
                abytBuf(0), _
                ByVal pBuf, _
                lngLen)
        ' return the buffer
        fStrFromPtrW = abytBuf
    End If
End Function


Function ReturnUserName() As String
' returns the NT Domain User Name
Dim rString As String * 255, sLen As Long, tString As String
    tString = ""
    On Error Resume Next
    sLen = GetUserName(rString, 255)
    sLen = InStr(1, rString, Chr(0))
    If sLen > 0 Then
        tString = Left(rString, sLen - 1)
    Else
        tString = rString
    End If
    On Error GoTo 0
    ReturnUserName = UCase(Trim(tString))
End Function

 
Function ReturnComputerName() As String
    Dim rString As String * 255, sLen As Long, tString As String
    tString = ""
    On Error Resume Next
    sLen = GetComputerName(rString, 255)
    sLen = InStr(1, rString, Chr(0))
    If sLen > 0 Then
        tString = Left(rString, sLen - 1)
    Else
        tString = rString
    End If
    On Error GoTo 0
    ReturnComputerName = UCase(Trim(tString))
End Function

Select allOpen in new window

And there's all sorts of forms and reports that won't launch unless you are logged on as me or as the Domain Admin.  In the Open() event there's a nice Select Case ReturnUserName  No match, no open.  Period.

Distribute an accde/mde front-end, and having your UI's recordset come from code, and nobody's going to get at that table that doesn't have your permissions/passwords.

Some things I keep a change log of.  You change a value, and the time, the machine, and username get logged.  It gets displayed, too, where needful.  It means that the mallet of loving correction doesn't have to get trotted out often.  Because nonsense has happened.  I was unhappy.  I fixed it.  Everyone was educated.  Repetition would be foolishness.  Stupidity, like height, hair color, and shoe size is innate -- we don't get to choose that, so it must be tolerated and compensated for.  Foolishness however, is a character flaw and subject to termination with extreme prejudice.
I'm just sayin
<grin>

@Nick,

Distribute an accde/mde front-end, and having your UI's recordset come from code, and nobody's going to get at that table that doesn't have your permissions/passwords.

As stated I dont want prevent users from seeing anything or even updating besides for that one column in one particular table.

Currently we have an ADP project that is being used as FE, and then an Access MDB with only linked tables, and users are free to create queries/reports or whatever they want.
I dont believe a FE solution will accomplish what I am currently looking for.

I guess my first option would be to look into what Scott suggested regarding column permissions (as you & Pat agreed), however waiting for more clarification on that approach.

Thanks,
Ben

Hi Experts,

Can someone please help me understand how do I apply Scott's suggestion in a way that only I have rights to update that column?

Thanks,
Ben

I've only used it in very rare cases, such as passwords (even when encrypted).

@Nick,
Got you!

@Scott,
At the moment I cant test it, however looks pretty straight forward.

Thanks,
Ben

Thanks to all participants!