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