A few users who has access to a database via a windows group which has modify rights, now also need to be able to access the database via SSMS but then only with read only rights. I therefor need to differentiate the rights on basis of the app they access the database with. Since the app has been running for a while and is not that easy to change, I have to come up with a way to prevent them from doing updates while working in SSMS.
Is there a way to do this? I am currently looking into whether it would be possible to use a SSMS application role in combination with a login trigger which executes sp_setapprole when the user logs in with SSMS.
Are there other ways to do this?
And if not, am I on the right track? I have very little experience in working and managing application roles and have no idea whether my idea would work.
Thanks in advance