locked
Auto disable user on Ms. SQL DB RRS feed

  • Question

  • I would like to secure our database by auto disable user if he/she does not log in to database at any specific of time.

    Is there any possible way to do so ?

    Thanks and Regards,

    Vorleak

    Wednesday, June 21, 2017 9:30 AM

Answers

  • Hi,

    It's tricky question and you need to develop your custom scripts to do this. The below I can think

    1) Create one database
    2) Create one table with required columns
    3) Monitor the server logon activities and dump to the table created at step 2
    4) write the disable user statements based on the logon time details based on where condition in the table created at step 2
    5) If the statement returns true based on where condition, apply disable user script at database level

    OR

    1) You can query the server login times available in default trace
    2) If the base time lines are sufficient according to your requirement from default trace you can do approach disable users 
    NOTE 2.1) On defaults trace it is very limited timelines of data available

    Thansk,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    • Marked as answer by Vorleak Monday, July 10, 2017 6:38 AM
    Wednesday, June 21, 2017 9:49 AM

All replies

  • Hi,

    It's tricky question and you need to develop your custom scripts to do this. The below I can think

    1) Create one database
    2) Create one table with required columns
    3) Monitor the server logon activities and dump to the table created at step 2
    4) write the disable user statements based on the logon time details based on where condition in the table created at step 2
    5) If the statement returns true based on where condition, apply disable user script at database level

    OR

    1) You can query the server login times available in default trace
    2) If the base time lines are sufficient according to your requirement from default trace you can do approach disable users 
    NOTE 2.1) On defaults trace it is very limited timelines of data available

    Thansk,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    • Marked as answer by Vorleak Monday, July 10, 2017 6:38 AM
    Wednesday, June 21, 2017 9:49 AM
  • So you are saying you want to disable login of user if he does not logs in for certain period of time ?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by Vorleak Monday, July 10, 2017 6:37 AM
    • Unmarked as answer by Vorleak Monday, July 10, 2017 6:38 AM
    Wednesday, June 21, 2017 11:48 AM
  • You can enable successful logins in the security tab of SQL Server. Then you could query the SQL Server log to see when the user has logged in and do a group by date to see the days they have logged on. Similarly you could group by to get the hourly logins.

    Then it would be a matter of incorporating some logic to disable their account.


    Wednesday, June 21, 2017 12:07 PM
  • Thanks so much for your clear reponse.

    Nice Regards,

    Vorleak

    Monday, July 10, 2017 6:42 AM
  • Hi,

    Yes, I want to automatically disable SQL DB user who does not login to database at any specific of time.

    Thanks and Regards,

    Vorleak

    Monday, July 10, 2017 6:43 AM
  • Hello,

    I could not get the point you have mentioned.

    Could you help explain abit more detail ?

    Thanks.

    Vorleak

    Monday, July 10, 2017 6:47 AM