the answer is yes and no.
once I have a username and password, I'm good to connect with anything i can get my grubby fingers on...SSMS, a program i write, Access,Excel, Oracle's SQL Developer...even a vbs script...you name it.
the only way to prevent users from using a specific application to connect is to use a logon trigger...but that is based on the application name, which can be faked by any developer who has access to modifying the connection string.
here is an example from a previous thread, where someone wanted to prevent Access or Excel from connecting to the database. all you'd do is change the name to "Microsoft SQL Server Management Studio - Query " or "Query Analyzer" or any other app to exclude..excell, access, LinqPad....
you don't have to test just the application name...you could also make sure they were part of an admin group or some role as to whether they can connect with SSMS or not. for example you might want to allow anyone who is in a serverrole to connect anyway
note a logon trigger could prevent EVERYONE from connecting if you mess it up....
here's goes:
--Prevent access from Excel
CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
OR APP_NAME() LIKE '%Query Analyzer%'
ROLLBACK
END
--Try to connect from Excel/whatever app...you'll get an error...not very descriptive, but an error preventing login.
Lowell