Pages

Friday 30 July 2010

Unable to login to Navision after restoring database onto a new server

After restoring a Navision database from live server to the development server. it is not possible to login to the database using sa.

if you run the follwing sql statement, you will get
use [databaseName]
go
sp_change_users_login @action='Report'
go


UserName UserSID
dbo 0x90CEEB2B9F356D48B3EDFBFE736524C4

This shows that the username dbo is not associated with any logins. i.e. orphaned user. this happens because the logins are stored in the master database which is different from the master database on the development server.

changing the db owner of the database does not resolve this issue.
EXEC sp_changedbowner 'sa'

but fixed the orphaned user issue if the orphaned user is 'dbo'
so, after running EXEC sp_changedbowner 'sa', the database owner will be changed to 'sa' but you will still not be able to login to Navision with sa.

If you try to login using the Navision interface, you will get the folliwing message;

The user ID and password are invalid. Try again.

the only way i have found to solve this is to delete the users from the Navision [user} table. This is ok, if you are setting up navision on the develoment enviroment, but please be careful in deleting anything on produciton!!

so, in my case running the follwing command fixed the issue;
delete from [mydatabase].dbo.[User]