How to change security model through code in SSMS

Business Central/NAV Technical Forum
1

Hi All,

Can any one tell me how to change the security model (from enhanced to simple) in NAV from SQL Server Management Studio through query or wizard?

I know that this can be done from File>> Database >> Alter but is there any other way to do same?

I tried using the ALTER feature (enabling Single User Mode) but its taking more than an hour, is there any short-cut for doing this?

How much is the expected time for changing this in a databse of size about 1 GB, is 1 hour really long enough? Is there something wrong in it?

Thanks in advance.

2

changing to single user mode should be almost instantaneous, unless there was a HUGE transaction going on that eeds to be rolled back. I don’t think changing the security model in T-SQL is a good idea

3

Its taking more than 90 minutes and still running, shall I kill the session and try again?

The database is small and is in Single User mode, as that is a pre-requisite for Altering security mode.

Any suggestions?

4

Yes I would kill it, and try again. Did you set it to single user from SSMS or from NAV? From NAV it sometimes gets stuck, but I’ve never seen it get stuck in SSMS.

5

Hi Denster,

I was trying to set it to single user from NAV and that was taking time, in fact system was NOT RESPONDING, from NAV database>> information>> session it was showing 1 session but it seems some other process was using another session, so it was not able to make "single user " mode.

Later I tried to make “single user” from SSMS and it gave me error saying some session (more than 1) is in use, then I killed the session from SSMS, Activity monitor and did make it “songle user”, then changed security model to simple.

Following is the code for making 'single user ’ from SSMS:

ALTER DATABASE [Works] SET SINGLE_USER WITH NO_WAIT

OR

USE master

GO

EXEC sp_dboption ‘Works’, ‘single user’, 'true’

Denster, Thanks for your time.

6

Yep setting that property directly in NAV sometimes gets stuck, happened to me a few times [:^)]. Glad you got that to work and thanks for following up.

Personally I like using the properties pages. Right click the database, go to the Options page, and all the way down you’ll see a property called “Restrict Access”, which I would set to ‘SINGLE_USER’ there. That has always been almost instantaneous for me.

7

HI,

I could not fine this, please let me know how to do it.

In NAV I was using Database>> Alter>>Options>> Single User, which was NOT RESPONDING as mentioned earlier…

8

In SSMS:

9

Yes, got your point, that is another way of doing it and perhaps the best way of doing it.

10

Just because that’s how I do it, that does not make it the best way [H]

When you set the property that way, internally it fires this query:

ALTER DATABASE [NAVDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

I like using the property pages because it eliminates typing errors.