CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2008-r2
crsuarezf
crsuarezf

Reputation: 1271

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

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:

First Connection to database_1:

Second Connection to database_1:

User some_user trying to execute some query:

Query 1: 

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

Example 2:

First Connection to database_1:

Second Connection to database_1:

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.

Upvotes: 5

Views: 1543

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294407

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.

Upvotes: 7

Related Questions