May 17, 2019 at 7:03 pm
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
May 17, 2019 at 7:06 pm
Out of interest, what is stopping him from connecting right now?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 17, 2019 at 7:17 pm
You can explicitly deny write access to his specific login.
Wouldn't that also (badly) affect his rights in GP?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 17, 2019 at 7:21 pm
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.
May 17, 2019 at 7:28 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 17, 2019 at 7:43 pm
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.
May 17, 2019 at 7:49 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 17, 2019 at 8:03 pm
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
May 22, 2019 at 1:05 pm
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply