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

Question

Hello Everyone, Please help me with this issue, I've to write a stored procedure which will disable all the database user(not the admins like - sa) from 8.30 am to 7:00 pm. If anyone wants more details on this please feel free leave your question. Thank you

Ritendra Mall · Answer

Hello Ramachandran M, Could you please help me to understand i've a script for closing all the sessions of a specific DB user :- DECLARE @kill varchar(8000) = ''; SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';' FROM sys.dm_exec_sessions WHERE login_name = 'Riten' Print @kill EXEC(@kill) But when i exec it i am getting below error :- Must declare the scalar variable "@kill" please help me to understand this . Thank you

Ritendra Mall · Answer

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

Ramachandran M · Answer

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 2. GRANT CONNECT TO "sa" 3. update sys.server_principals set is_disabled = 0 where name='sa'

Ritendra Mall · Answer

@Ramachandran M The requirement is to disable/enable a particular DB user for a certain duration

Ritendra Mall · Answer

@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 ?

Ramachandran M · Answer

TRY THIS IF((@TimeHour > 8 AND @TimeMinites > 30 ) AND (@TimeHour BEGIN ALTER LOGIN login_name DISABLE END ELSE BEGIN ALTER LOGIN login_name ENABLE END refer: https://stackoverflow.com/questions/11300760/lock-sql-login-programmatically

Ramachandran M · Answer

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 BEGIN REVOKE CONNECT FROM "sa" END ELSE BEGIN GRANT CONNECT TO "sa" END END

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

Answers (7)