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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With