Update a column value for system table

Question

Update a column value for system table

Pink123

Default port

Points: 1486
More actions
April 13, 2012 at 11:14 am

#146893

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,

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 More actions · Answer 2

Thanks for the reply.

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

Lowell SSC Guru Points: 323504 More actions · Answer 3

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: 1004504 More actions · Answer 4

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 More actions · Answer 5

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

Lowell SSC Guru Points: 323504 More actions · Answer 6

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: 1004504 More actions · Answer 7

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 Old Hand Points: 300 More actions · Answer 8

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: 42192 More actions · Answer 9

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 Old Hand Points: 300 More actions · Answer 10

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.