Post reply

Lock login at specific date

  • February 19, 2009 at 12:48 am

    #197298

    Is there any function in SQL Server 2005 to set "lockdate" for a login. User Adam finish his job 30 July and I want his login to be locked that date.

  • February 19, 2009 at 6:14 am

    #946592

    If it is Windows user, then you can set an expiry date for the Windows user. This should do the trick. But I am not 100% sure about it.

    -Roy

  • February 19, 2009 at 7:00 am

    #946630

    Thanks Roy. I use Sql server authenticating.

    Regards

  • February 19, 2009 at 7:19 am

    #946652

    You can try this. I have never done this before. This will work only with Windows server 2003 and higher with SQL server 2005 or higher.

    USE [master]

    GO

    CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

    GO

    -Roy

  • February 21, 2009 at 12:53 am

    #948117

    If these are sql logins and not windows logins you could create a job that executes the following t-sql at the disable time

    ALTER LOGIN UserNameHere DISABLE

    And then another job that runs at the enable time executing the following...

    ALTER LOGIN UserNameHere ENABLE

  • February 21, 2009 at 6:31 am

    #948145

    you could also use a logon trigger that checks the login against getdate() & some expiration date in a table, and use that logon trigger to prevent logging in.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 22, 2009 at 2:43 pm

    #948360

    The only gotcha with logon triggers is if there is an issue with one, it will prevent all logons except through the DAC.

    Another option would be to build a small SQL Server Agent job that will run and execute the DISABLE LOGIN query to disable the SQL Server-based login. If you have a lot of these, you might want to create a small work database that contains a table where you can put the logins and when they expire and then have the SQL Server Agent job run once a day, cycle through the table, and disable the logins on the appropriate day.

    Otherwise, the general recommendation, if possible, is to switch them to Windows logins. That would be preferred, as then all security is controlled by Active Directory (assuming the environment is large enough to have a domain).

    K. Brian Kelley
    @kbriankelley

  • February 22, 2009 at 11:35 pm

    #948436

    Thank's all of you! It gave me many tips to solve the problem!

    Tomas

  • August 29, 2009 at 10:30 pm

    #1046571

    I wnat a sql login to be locked automatically after 3 uncessfull attempts is there any way to do the same.

  • August 30, 2009 at 6:20 am

    #1046599

    Set the windows login policy for local computer and then make the sql login with enforce password policy (as described in posts above).

    SQL Server will take password policy (like expiration, how many failed attempt are permitted. password stregth etc) from Windows.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply