We have a situation where Developers do not have any
UPDATE permissions, BUT they work with applications and see connection strings -> they know passwords from some SQL accounts (example
SQLLogin1) that have UPDATE permissions. Our operations currently are not perfect, and sometimes production data needs to be modified (no GUI for that yet).
Instead of contacting DBA, and asking him to modify the data, Developer would (improperly) use SQL account
SQLLogin1 (that has permission to modify the data),
and connect over SQL Server Management Studio to modify the data himself.
DBA can not change password for
SQLLogin1 without Developer seeing the new connection string and new password, since the application connection string that uses
SQLLogin1 is maintained by Developer.
Is there a way to deny access to
SQLLogin1 SQL login, but only if it is connecting over SSMS?
At the same time if
SQLLogin1 is connecting over
.Net SqlClient Data Provider (
program_name in the
sys.dm_exec_sessions), it must be allowed to login.
This way we want to not let Developer connect over SSMS using
SQLLogin1, while the application that is using
SQLLogin1, would still be able to connect.