NEED TO UPDATE A SID FIELD IN SYSLOGINS

[mbfloyd]

Hi, I am trying to update a sid field of a database I moved from another server. using

update sys.syslogins
set sid=0x1E05CBC760218744B42F05FD36E6E333 where name = 'WORKSITEMP'

I am getting this error:

Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.

what do I need to do? I have read numberious solutions online and nothing has worked. Please help!!!

Thanks Mack

 

[danvlas]

Have you tried this?

http://www.sqlservercentral.com/scripts/Backup+/+Restore/31477/

Read carefully before proceeding!


HTH



Daniel Vlas
Systems Consultant

 

[mbfloyd]

I read it VERY cafefully, followed instructions and I got:

Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.

Msg 259, Level 16, State 1, Line 2
Ad hoc updates to system catalogs are not allowed.

Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

Thanks for your reply and help. Why is this a problem to get done.?

Thanks
Mack

 

[danvlas]

Well, probably it is a security issue.

See if "sp_change_users_login" stored procedure can fix the problem (especially with the Auto-Fix option)

Excerpt from BOL:
"Links a user entry in the sysusers table in the current database to a SQL Server login of the same name. If a login with the same name does not exist, one will be created. Examine the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.

When you use Auto_Fix, you must specify user and password if the login does not already exist, otherwise you must specify user but password will be ignored. login must be NULL. user must be a valid user in the current database. The login cannot have another user mapped to it."

Look it up in BOL before running:
EXEC sp_change_users_login 'Auto_Fix', 'WORKSITEMP', NULL, 'PasswordIfNewLogin';


Hope this helps.


Daniel Vlas
Systems Consultant