Set Max number of Queries to specific database user in SQL Server 2008 R2

Question

Is there a way that a database user can be restricted to do one connection to a database and only one query at time? -In SQL Server 2008 R2 -

Example 1:

• Database: database_1
• Username: some_user
• Password: some_pass

First Connection to database_1:

• Connection: xxx.xxx.xxx.xx1
• ...Connected to xxx.xxx.xxx.xx1

Second Connection to database_1:

• Connection: xxx.xxx.xxx.xx2
• ...Failed to connect: xxx.xxx.xxx.xx2

User some_user trying to execute some query:

Query 1:

select * 
from table1 
join ,...,join tableN

Example 2:

• Database: database_1
• Username: some_user
• Password: some_pass

First Connection to database_1:

• Connection: xxx.xxx.xxx.xx1
• ...Connected to xxx.xxx.xxx.xx1

Second Connection to database_1:

• Connection: xxx.xxx.xxx.xx2
• ...Connected to xxx.xxx.xxx.xx2

User some_user trying to execute some query by using connection: xxx.xxx.xxx.xx1

Query1:

 select * from table1 join ,...,join tableN

Result: executed... N records returned

User now tries to execute another query by using connection: xxx.xxx.xxx.xx2

Query2:

select * from table1 join , ... , join tableN

Result: not executed... can't executed more than once query at a time.

Answer 1

You can create any access policy you like using logon triggers:

You can use logon triggers to audit and control server sessions, such as by tracking login activity, restricting logins to SQL Server, or limiting the number of sessions for a specific login.

See the link for an example. If you limit a user to only one connection at a time then you have effectively limited the user to only one query at a time since, for all practical purposes, a connection can only run one query (w/o going into MARS details).

A related concept you need to be aware is the Resource Governor, which allows you to limit the resource a user can consume by assigning user sessions to specific workloads and limiting the workload resource allocation. Again, see the link for details and examples.