Post reply

Help with method to lock a login ...

  • May 6, 2008 at 2:18 pm

    #182025

    I have a process which reads the login list and the login status from master..sysxlogins.

    I am comparing the status of the login with an internal authoritative source to determine if the login should have access.

    When I determine that a login should not have access, I need to lock the login to disallow connection to the SQL Server instance.

    I will later come back and drop the login after some period of time.

    I cannot find any system stored procedure which will perform this locking action for me at the login level.

    Is my only option to "update master..syslogins set denyaccess = 1 where name = 'login_name'" ??

    Thanks in advance ...

    Azureal

  • May 6, 2008 at 2:39 pm

    #811484

    Do not update the system tables. It can have nasty side effects and is extremely risky.

    Windows or SQL login?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 6, 2008 at 2:57 pm

    #811491

    Almost exclusively SQL logins.

    I have been reading and see that for Windows Domain accounts sp_denylogin can be used, but I tried it and it does not work for SQL accounts.

    I also got my hopes up when I found something about "ALTER LOGIN 'login' DISABLE" but quickly figured out that it is for SQL 2005 ... 🙁

    Thanks for your help Gila

    Azureal

  • May 6, 2008 at 3:22 pm

    #811496

    Can you change the password? Not perfect, but should suffice.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 7, 2008 at 8:27 am

    #811827

    Thanks Gail,

    That is certainly an option and you are correct, it would suffice. I just find it difficult to believe that SQL server provides no mechanism for the DBA to lock a login.

    Knowing that the login will be dropped after 90 days, would you change your opinion regarding setting denyaccess to 1 for the login?

    So that I fully understand the ramifications of updating fields in the sysxlogins table, can you be more specific about the nasty side effects?

    I am just curious really. I think your idea about changing the password may be the easiest solution and present the smallest risk to the system.

    Thanks again for you help!

    Azureal

Viewing 5 posts - 1 through 4 (of 4 total)

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