SQL Server connection restriction

Question

Is there any way to restrict individual connection to SQL Server Management. Like I have set of users can login to SQL server Management studio with active 2 connection for a individual open in a time. Connection with Database will not be allowed if its more than two.

Thank You

No, and why would you. SQL server can handle many connections.... — Mitch Wheat, Jan 30 '13 at 06:34
yes but need as CPU utilization goes high for maximum connection. — user1954762, Jan 30 '13 at 06:36
this is possible in oracle... but not sure about it's feasable to SSMS. — user1954762, Jan 30 '13 at 06:37
CPU is not directly related to number of connections: it's what teh connections are doing... — Mitch Wheat, Jan 30 '13 at 06:38
maximum open connection with different SPID will leads to high CPU utilization in DB server. — user1954762, Jan 30 '13 at 06:41
then the reason for CPU util for DB server is why going high.. could you pls suggest for possible reasons. — user1954762, Jan 30 '13 at 06:50

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

If you're trying to troubleshoot high CPU utilisation, then I think you're barking up the wrong tree. A google search on "high cpu sql server" will pull up this, among other things.

That said, you CAN limit ssms sessions with a logon trigger. You might want to modify the inner query to something like this:

SELECT 
    COUNT(*)
FROM 
    sys.dm_exec_sessions
WHERE 
    is_user_process = 1 
    AND program_name like 'Microsoft SQL Server Management Studio%'
    AND login_name = system_user

I don't know that it's going to get you what you want, especially since you're ultimate goal is tuning CPU. The trigger will fire for every logon, which might use more processing power than the extra sessions you'd be preventing.

Also, I can't recommend hitting an already laggy production server with this. You could replay a profiler trace from prod on a dev copy with and without this change and see if there's any significant difference. But if I were you, I'd start looking for missing indexes.

SQL Server connection restriction

1 Answers1