Changing Security Model/ Access in NAV on SQL

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

Recently one of my clients was facing problems in security synchronization (I will mention those problems in another blog), I observed that the database was using Enhanced Security model (which is Default option) and suggested to change it to Standard.

I generally prefer using Standard Security model, because enhanced  has some problems in synchronization, assigning user roles and you have synchronize on making any change to security/ role setup.


 

The security model can be changed from Database>> Alter >>Advanced Tab and there is one pre-requisite for this that database should be in single user mode. So I tried changing to “Single User” and “Standard” security model, but system got hung (Not Responding), waited for about 1 hour, whereas it is supposed to be instantaneous for small database.

Before doing this, Database>> Information>> Session was showing only one session.

Then I had to kill this session which was not responding and tried from SQL Server Management Studio by writing following query:

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

USE master

GO

EXEC sp_dboption 'DatabaseName', 'single user', 'true';

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

Then got the following error:

Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database 'Demo Database NAV (6-0)'
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
sp_dboption command failed.

Finally, had to use Activity Monitor (Ctrl+ Alt + A)in SQL Server Management Studio and kill the activites running related to that database, please note that from within NAV it was showing only one session. And then executed the above mentioned query and the database was altered to  "Single User" mode and then from within NAV chnaged the security model from Enhanced to Standard.

Another way of changing database to "Single User" mode is following:

Go to SQL Server Management Studio>> Database>> Select your Database>> properties>> Options  (see the following screen shot ):

After doing Security mode change to Standard, user forgot to change the Single User mode to Multi user, that gave another error when another user tried to access database.

 

When database is in "Single User" mode and more than 1 session is tried then following error occurs:

---------------------------
Microsoft Dynamics NAV Classic
---------------------------
The following ODBC error occurred:

 

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

 

---------------------------
OK  
---------------------------

 

Resolution: Change the database access mode from "Single User" to "Multi user", which can be done in three ways described earlier. Though the SQL query will change a little bit:

USE master

GO

EXEC sp_dboption 'DatabaseName', 'single user', 'FALSE';    // Note that True has now become FALSE.


Comment List
  • Thanks a lot. I had some troubles with the DB security properties. On importing fob-file into NAV I got error:

    15151,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot find the user '$ndo$shadow', because it does not exist or you do not have permission.

    And just changing security model restored that corrupted security settings.

Related
Recommended