Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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:

  • 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.

like image 288
crsuarezf Avatar asked Mar 24 '23 19:03

crsuarezf


1 Answers

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.

like image 150
Remus Rusanu Avatar answered Apr 05 '23 20:04

Remus Rusanu