Re: Migrating a network installation from MSDE to SQL Server
I've experimented with doing this, following the instructions on p.9 of the Network Installation Guide, and assuming the latter concerns the situation the default SQL server authentication method has been chosen, since the instructions make reference to enabling the NBNUser login.
There was no problem in detaching the NBNData database from MSDE and attaching to SQL Server (steps 1 9). My probems started at step 10, "Enable the NBNUser login etc." I suspect there is a fundamental problem here in that although you have copied over an NBNUser login with the database, there is no corresponding server-level NBNUser login.
This is what I did:
I firstly tried running 'EXEC sp_change_users_login "Update_One", "NBNUser", "NBNUser"' without creating a matching server level login ( as implied in the installation guide). This gave the error 'Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108
Terminating this procedure. The User name 'NBNUser' is absent or invalid.'. So I deleted NBNUser in NBNData (it won't let you delete while the user still owns a Schema so I temporarily transferred ownership of the NBNUser schema to another user). I then created a new server level login NBNUser, with permissions matching those in MSDE, and mapped this to NBNData, thus creating a new NBNUser on the database. I changed permissions to match the original. I then re-ran the script just to make sure, but still got the "invalid user" message. (Whats even more puzzling is that I have no problem logging in as this user from Management Studio).
If I install Recorder to use Trusted Connection, there is no problem, since NBNUser presumably is not used.
Any comments please?
Alan
Aberystwyth