Unique Permissions for User

  • blalor

    Valued Member

    Points: 62

    We have a financial application (Dynamics GP) with a SQL 2014 back-end.  Users of GP are in a security group that has write/edit permissions.  Ownership is asking me to give one user, who is a member of that group, read-only access to the SQL tables so he can connect via MS Access and ODBC.   SQL has Windows authentication and so nothing I have tried stops him from having the ability to edit data via Access.

    Any suggestions?

    Thanks

  • Phil Parkin

    SSC Guru

    Points: 243543

    Out of interest, what is stopping him from connecting right now?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • ZZartin

    SSC-Dedicated

    Points: 30338

    You can explicitly deny write access to his specific login.  You could also create a SQL login with read only access and use that for his ODBC connection instead of using windows auth.

    • This reply was modified 3 months ago by  ZZartin.
  • Phil Parkin

    SSC Guru

    Points: 243543

    ZZartin wrote:

    You can explicitly deny write access to his specific login.

     

    Wouldn't that also (badly) affect his rights in GP?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • ZZartin

    SSC-Dedicated

    Points: 30338

    Phil Parkin wrote:

    ZZartin wrote:

    You can explicitly deny write access to his specific login.

      Wouldn't that also (badly) affect his rights in GP?

     

    Yeah the deny on his personal login would trump the read/write group access which may or may not be intended.  So if the idea is for him to keep read/write access but only have read access in Access :p another option would be to make a SQL login with read only access and use that for his ODBC.

  • Phil Parkin

    SSC Guru

    Points: 243543

    ZZartin wrote:

    Phil Parkin wrote:

    ZZartin wrote:

    You can explicitly deny write access to his specific login.

      Wouldn't that also (badly) affect his rights in GP?

      Yeah the deny on his personal login would trump the read/write group access which may or may not be intended.  So if the idea is for him to keep read/write access but only have read access in Access :p another option would be to make a SQL login with read only access and use that for his ODBC.

    Which would work ... as long as he does not twig that he can also log in directly using Windows Auth.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • ZZartin

    SSC-Dedicated

    Points: 30338

    Phil Parkin wrote:

    ZZartin wrote:

    Phil Parkin wrote:

    ZZartin wrote:

    You can explicitly deny write access to his specific login.

      Wouldn't that also (badly) affect his rights in GP?

      Yeah the deny on his personal login would trump the read/write group access which may or may not be intended.  So if the idea is for him to keep read/write access but only have read access in Access :p another option would be to make a SQL login with read only access and use that for his ODBC.

    Which would work ... as long as he does not twig that he can also log in directly using Windows Auth.

     

    Which kind of raises the question of why he would need to have different permissions in access vs whatever other application.  Or if there are only certain tables he needs access to deny write access on everything but those.

    • This reply was modified 3 months ago by  ZZartin.
  • Phil Parkin

    SSC Guru

    Points: 243543

    Which kind of raises the question of why he would need to have different permissions in access vs whatever other application.  Or if there are only certain tables he needs access to deny write access on everything but those.

    I think it's a fairly safe bet that he uses GP for controlled and audited data modifications (OLTP), and wants to use other tools (eg, Access) for read-only reporting purposes.

     

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Sue_H

    SSC Guru

    Points: 90005

    Phil Parkin wrote:

    I think it's a fairly safe bet that he uses GP for controlled and audited data modifications (OLTP), and wants to use other tools (eg, Access) for read-only reporting purposes.  

     

    Even though it's a request for reads, that can actually be risky in terms of audits and this being a financial application. The poster should make sure they have the request in writing - change ticket, email or something. And make sure whoever is requesting the extra access is aware of the risk issues as well.

     

    Sue

     

  • Chris Wooding

    SSCarpal Tunnel

    Points: 4227

    If GP does all its writes via stored procedures, you can deny write to his login and grant execute to the stored procedures.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply