Error: [Microsoft][ODBC SQL Server Driver]Database is invalid or cannot be accessed State ID: HY024

What a great title for a blog post?

I got this error when a customer had tried updating their NAV 4.0 SP3 to use Standard security as opposed to Enhanced.

I suggested all sorts of fancy SQL to resolve the situation thinking it was a permissions error as I know that changing a database to use Standard causes some problems on permissions with the Session and Database File view.

It turns out the user had left the database in single user mode - a pre-requisite of being able to change the security model.

I used Activity Monitor in SQL 2005 Management Studio to kill the connected process, then logged in to NAV and used File, Database, Alter to remove the single user mode from the database. Problem sorted. But what about those missing permissions?

If you create a NAV database with standard security, the system will allocate permissions to the Database File and Session views to the application server role $ndo$shadow. However, if you change a database from Enhanced to use Standard, these permissions do not get assigned and you therefore hit errors unless you are a db_owner.

To resolve this you can grant the permissions yourself as follows:

GRANT DELETE ON [dbo].[Database File] TO [$ndo$shadow]
GRANT INSERT ON [dbo].[Database File] TO [$ndo$shadow]
GRANT REFERENCES ON [dbo].[Database File] TO [$ndo$shadow]
GRANT SELECT ON [dbo].[Database File] TO [$ndo$shadow]
GRANT UPDATE ON [dbo].[Database File] TO [$ndo$shadow]
GRANT DELETE ON [dbo].[Session] TO [$ndo$shadow]
GRANT INSERT ON [dbo].[Session] TO [$ndo$shadow]
GRANT REFERENCES ON [dbo].[Session] TO [$ndo$shadow]
GRANT SELECT ON [dbo].[Session] TO [$ndo$shadow]
GRANT UPDATE ON [dbo].[Session] TO [$ndo$shadow]