Stored procedure to disable login from 8:30 a.m to 7:00 pm

Hello Ramachandran M,

 

Could you please help me to understand i've a script for closing all the sessions of  a specific DB user :-

 

 

But when i exec it i am getting below error :-

Must declare the scalar variable "@kill"

 

please help me to understand this .

 

Thank you  

Suppose i want to disable only a particular DB user for a specific time not the others ? 

1. Create  SQL Job with duration 30 mins and add below created procedure

following query we use to disable the user 

1.ALTER LOGIN login_name DISABLE

2. REVOKE CONNECT FROM "sa"

3. update sys.server_principals set is_disabled = 1 where name='sa'

 

To Enable

 

1.ALTER LOGIN login_name ENABLE

@Ramachandran M

 

 

Suppose what if we've to disable the list of the user's as i told you except 'sa' so how we will modify the below mentioned script as per this condition ? 

TRY THIS

 

IF((@TimeHour > 8 AND @TimeMinites > 30 ) AND (@TimeHour < 19 AND @TimeMinites < 0 )) 

END

 

refer: 

https://stackoverflow.com/questions/11300760/lock-sql-login-programmatically

try this

 

CREATE PROCEDURE ENABLEDISABLEUSER
AS
BEGIN

DECLARE @TimeHour AS INT
DECLARE @TimeMinites AS INT
SET  @TimeHour = (SELECT FORMAT(getdate(), 'HH'))
SET  @TimeMinites = (SELECT FORMAT(getdate(), 'mm'))
PRINT @TimeHour
PRINT @TimeMinites
IF((@TimeHour > 7 AND @TimeMinites > 30 ) AND (@TimeHour < 19 AND @TimeMinites < 0 )) 
BEGIN
REVOKE CONNECT FROM "sa"
END
ELSE
BEGIN
GRANT CONNECT TO "sa"
END

END