We use SQL Server 2005. We have created a SQL Server Login called RUNSQLQUERYUSER. We have the following questions:
1. How to limit RUNSQLQUERYUSER from executing SQL queries using SQL Server Management Studio between 9:00 AM and 5:00 PM? How to implement that? Therefore if RUNSQLQUERYUSER runs a query at 8:00 AM we let them but after 9:00 AM we don't let this user execute queries.
2. If RUNSQLQUERYUSER runs a SQL query before 9:00 AM but the query takes more than 30 minutes we need to stop it.
3. If RUNSQLQUERYUSER runs a SQL query before 9:00 AM but the query is still running at 9:00 AM we need to stop it.
Alternatively you can use a logon trigger to prevent that user from even connecting to SQL Server during those hours. It has more flexibility than permissions as you can also check which application it's using, which machine it's connecting from, etc.
And for future reference, if you upgrade to SQL Server 2008 Enterprise Edition, you can use the Resource Governor to control how many resources are used for certain users/connections.
How to restrict Users from running SQL.
Posted - 08/31/2012 : 15:33:24
