Update a column value for system table

  • Pink123

    Default port

    Points: 1486

    Is there a way to update a column values for system table

    There is a system table syslogins ,Column updatedate.

    Can I manually update the updatedate column for this system table.

    If I try to do so it doesnt allow if to change system table data.

    I need to test a few scenarios in my development region and for that I need to change the update date.

    Please help.

    Thanks,

  • Gail Shaw

    SSC Guru

    Points: 1004446

    No, the system tables cannot be updated. Besides, syslogins is not a table, it's a view.

    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
  • Pink123

    Default port

    Points: 1486

    Thanks for the reply.

    Even for some test purposes .Is there a way to change the updatedate in syslogins manually?

  • Lowell

    SSC Guru

    Points: 323442

    ALTER LOGIN bob DISABLE;

    ALTER LOGIN bob ENABLE;

    after you've disabled and re-enabled the login, the updatedate will be the moment the last event occurred( the enable)

    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!

  • Gail Shaw

    SSC Guru

    Points: 1004446

    You have to alter the login to change the date. The system tables cannot be directly updated.

    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
  • Pink123

    Default port

    Points: 1486

    Thanks but if I want to set the update date as some date in past ,how do I do that?

  • Lowell

    SSC Guru

    Points: 323442

    Pink123 (4/13/2012)


    Thanks but if I want to set the update date as some date in past ,how do I do that?

    sounds way too much like you are trying to falsify records for some reason.

    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!

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Pink123 (4/13/2012)


    Thanks but if I want to set the update date as some date in past ,how do I do that?

    Short of playing with the machine date (which will likely cause all sorts of problems with any auditing or security stuff), you can't.

    What exactly are you trying to do?

    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
  • berokke1

    SSC Veteran

    Points: 220

    I have a co-worker who wants to just update the login name instead of adding the new domain\login name for active directory groups that have been switched to a new domain.  Is it better to drop and recreate the login when the domain changes or just alter login?

  • Chris Harshman

    SSC-Forever

    Points: 41845

    You will want to recreate the logins instead of renaming them, since Windows authenticated logins have a sid in the sys.server_principals that ties directly to the objectSid of the AD user or group.

  • berokke1

    SSC Veteran

    Points: 220

    Unfortunately, it will authenticate because of the SID history that is kept with the new windows login - The  SID history is moved from the old login to the new so it will authenticate it correctly.  So he wants to just update the name in syslogins.  I'm totally opposed however, if I don't have a good reason, the powers that be will side with him as most have this "its good enough" approach.  I'm more meticulous and would like to do it the correct way so that we have the New SID that belongs to the new domain login.

    From what I read, even doing an Alter Login will not update to the new SID.  So I think we need to Drop the old login from the old domain and add the new login from the new domain.

Viewing 11 posts - 1 through 11 (of 11 total)

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