Enabling Ad hoc updates to the system catalogs?

[skhoury]

Hello all,

I have a slight problem...here is the scenario:

I am going to rebuild my database server. So natuarlly I backup all my databases, however when I do the restore it doesnt restore the login ids/passwords. When I go to create them, it claims that they already exist.

So I run a query against the sysusers table on my of db's, and what do you know....the user is listed in it. If I try to delete it (so I can recreate it via the gui), it says the following:

"[Microsoft][ODBC SQL Server Driver][SQL Server]Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this."

Any thoughts on how to enable this mode?

many thanks in advance!

Sam

 

[nigelrivett]

Bad way to try and fix it.

The problem is caused by the login id not matching that expected by the user entry.

You can execute a drop user then add the user or try sp_changeuserslogin

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[skhoury]

nigelrivett,

Thanks for your response. Im by no means a SQL pro....so I guess my question is: How do I execute a drop then add of a user?

Many thanks!

Sam

 

[nigelrivett]

Problem - mapping of logins to users

Have a look at master..syslogins
select name, sid from master..syslogins

Your login will be there.
The sid will map to a user in the database
use mydb
select name, sid from sysusers

you need to drop that user
sp_dropuser 'username'

Also check if there is a user with the name that you are trying to add - if so drop that user too.
You shuold now be able to add the user to the database.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.