I want to restrict a sql server authentication user's logging in from Management Studio by using a logon procedure instead of a logon trigger. How can I do that?
There is no reliable way to do this, unfortunately. The things you want to go by is set in the connection string and, hence, can be spoofed. Here's an example what I did in SSMS:
SELECT session_id, program_name FROM sys.dm_exec_sessions WHERE is_user_process = 1
With the result:
session_id program_name 51 MyCRM 52 MyCRM 53 MyCRM 54 MyCRM 55 MyCRM 56 SQLServerCEIP