none
in access vba change field permissions on sql table

    Question

  • I have a database with backend sql and front in access 2013. I want to be able to add the current user automatically to a field in a form.  Don't want the user to be able to go through the table to edit. Need to write some code that when it adds this code it will allow edit permission from the sgl server for that particular field, because I have made the permission for that field denied for update.  Need code that after it adds the user name it change the permission back to denied.

    Can someone lead me to the correct directions for this, do appreciate it.

    Thanks  in advanced

    Wednesday, October 09, 2013 4:28 PM

All replies

  • You may be approaching this in a difficult way. Changing permissions on an object requires elevated privileges. While you are seeking to secure an object with permissions, this method actually requires that the account have enough authority to change permissions on an object.

    An alternate approach might be to place the user's logon account (or the common logon account) in a role that does not have write permissions to the object. Create an SQL Stored Procedure that will then update the record. Grant the user execute permissions on the stored procedure.

    Here is the scenario. In your database, create a database role called "NoWrite", for example. Do not grant or deny any permissions to the database role. Create a database user, if not already existing, mapped to the login credentials for your SQL Server. Add the user to this role.

    Create a stored procedure that updates the table you wish to modify. In the procedure, do the actual update. Grant the user execute permissions on the Stored Procedure.

    To update the particular field then, is only possible using the stored procedure. You can test this by executing an update statement whilst logged in as your SQL Server user. It will be refused with an error. Put that same statement in a procedure and grant execute on the procedure, and it will work.

    Hope this helps.

    Wednesday, October 09, 2013 4:43 PM
  • I have a button in access that I click on. It adds a new record gives a unique number to that record and it takes the current user logged in on that computer and puts their name in a field. I don't want them to manually be able to edit that field. So I have done like you said and set up nowrite role and added users to that role on the SQL server. Could you help get started with the procedures.

    thanks


    • Edited by Treasur Wednesday, October 09, 2013 6:48 PM
    Wednesday, October 09, 2013 6:47 PM
  • I created a stored procedure for play and then when into database and ran the stored procedure. Although haven't got the restriction working correctly yet.  But was trying to get the idea of what you was saying to work.

    Can now work on the permissions and the actual table I need modifying.

    Thursday, October 10, 2013 1:06 PM
  • I have the stored procedure on the sql server working and the execute command from access working.

    The problem I am having on the sql server is if I don't give connect privileges to the sql database the user cannot open from access the form or table.

    Then when I give them connect privileges they automatically inherit write privileges. And I denied write privileges to the field can not execute the stored procedures

    Please help.

    Thursday, October 10, 2013 10:10 PM
  • Hi Treasur,

    First, I want to inform that this forum is to discuss SQL Server questions. I will help you troubleshoot the issue on SQL Server side. Regarding to your question:

    The problem I am having on the sql server is if I don't give connect privileges to the sql database the user cannot open from access the form or table.
    Then when I give them connect privileges they automatically inherit write privileges. And I denied write privileges to the field can not execute the stored procedures

    I want to confirm why the user has connect privileges will inherit write privileges. Usually, we create a SQL login within public role, and a corresponding database user without specifying permission explicitly, this user won’t have permission to access the table. Based on your requirement, I suggest creating the login and database user without any explicit permission, and then grant the stored procedure execute permission to this user, for example:

    USE DBName; 
    GRANT EXECUTE ON OBJECT::SchemaName.SPName
        TO UserName;
    GO

    Additionally, please provide more detail information about how you create login and database user here for analysis. For more detail information, you can refer to the following link:

    Grant Permissions on a Stored Procedure
    http://technet.microsoft.com/en-us/library/ms345484.aspx


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Friday, October 11, 2013 4:40 AM