Is it possible to restrict the number of connections for a role/login in SQL Server 2005?

Question

e.g. Make it so that you could limit it to a max 2 connections for a given technical account, the third connection being blocked repeatedly until one of the others is given up

Answer 1

To my knowledge this is not possible through the SQL Server Security implementation itself.

You can however, globally configure the maximum number of connections to SQL Server.

I believe would need to control/filter/screen all connections to SQL Server, externally to the database engine, in order to achieve this.

Answer 2

You can't do it declaratively on the server side. You could certainly do it in tsql code if you want to trust the application to enforce itself.

Answer 3

Yes but you need a tiny bit of SQL code. You can enforce any policy you like by creating a Logon Trigger. This trigger is new to SQL Server 2005 SP2 (it is not in RTM version afaik) and is run every time a connection is established, before any statement is accepted from said connection. Make sure you use a proper EXECUTE AS clause for your trigger. In the trigger you can inspect sys.dm_exec_sessions and sys.dm_exec_connections and decide if the new connection is allowed yes/no. Pleas note that on SQL 2005 the number of sessions is not necessarily equal with the number of connections, because of MARS. You should decide your policy if you allow 2 connections or 2 sessions.