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?

  • Make the trigger call your procedure. But this is typically not a good idea to limit access to the database. There are gazillion other SQL tools out there that can be used instead of SSMS. The only safe way to make your database secure is to create users with restricted privileges. That will be safe independently of the tool they are using. – a_horse_with_no_name Mar 27 '20 at 8:53
  • @a_horse_with_no_name Yes. I definetely agree with you. But, Unfortunately I don't have any chance to restrict the user on the server. The worst thing is that user has a sysadmin right and most of developers know its password because of connection strings in their config files. The user is used under most of internal project and this is the why i can not restrict its privileges. Now, i have got to block the requests from management studio with that users. I created my procedure in master db to get the requests but it did not work with that way you said. – DBA Mar 27 '20 at 9:16
  • Still, it won't keep the devs out as they will simply switch to a different tool. – a_horse_with_no_name Mar 27 '20 at 9:19
  • Yep, i am aware of it and i talked about this situation to my it department manager. He said if there would be an event then we take an action for the situation. Unfortunately the approach is like this. :/ – DBA Mar 27 '20 at 9:23
  • Have you considered using a firewall to block connections from portions of the network? – David Browne - Microsoft Mar 28 '20 at 0:39

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:

enter image description here

I execute:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.