View Server State

ta5

Hello
I have seen a strange behaviour 2 or 3 times when technically migrating older Nav solutions to Nav 2009. The users without special sql server roles needed to have sql rights on VIEW SERVER STATE. There are some threads around this topic, but I would like to see the case in a greater contecxt, I'm sure other reader will be also glad about this.

My question to this:

Is this a new prerequisite for Nav 2009? Traceflag 4616 is not needed anymore, the system views are now used for this? Does the VIEW SERVER STATE need to be granted manually? Does the security model of Nav matter? Is there a difference between db logins and windows logins concerning the topic?

Many thanks in advance.
Thomas

Slawek_Guzek

ta5 wrote:

Is this a new prerequisite for Nav 2009?

Yes

Traceflag 4616 is not needed anymore, the system views are now used for this?

What do you mean by 'this' ? T4616 was used to relax a little bit security to allow application role to see server state via some views/system tables contained in other than dbo schemas. VIEW SERVER STATE does more-less the same, however I suspect that different objects are accessed by NAV client, so different requirements are now in place.

Does the VIEW SERVER STATE need to be granted manually?

Yes if you move or upgrade database. I am not sure in other cases like creating new databases - consult documentation. BTW for manual permissions use GRANT VIEW SERVER STATE TO [public].

Does the security model of Nav matter?

No

Is there a difference between db logins and windows logins concerning the topic?

It depends. If you use GRANT VIEW SERVER STATE TO [public] then no.

Hope this helps.

Regards,
Slawek

stryk

The purpose of Traceflag 4616 is actually, to grant VIEW SERVER STATE permission to all logins/users and is required for the login process of NAV; hence it is a prerequisite for NAV since 4.0 SP3 U6 (see "Installation & System Management (SQL)" PDF document)
The login mechanism has been changed with NAV 2009 SP1 - from that version on you don't need the TF anymore.

See also: http://blogs.msdn.com/b/german_nav_developer/archive/2009/03/17/trace-flag-4616-aktivieren-auf-dem-sql-server.aspx (German)

So basically you don't have to grant VIEW SERVER STATE permission manually; just the TF should be sufficient enough. Does this work?

Slawek_Guzek

stryk wrote:

The purpose of Traceflag 4616 is actually, to grant VIEW SERVER STATE permission to all logins/users and is required for the login process of NAV; hence it is a prerequisite for NAV since 4.0 SP3 U6 (see "Installation & System Management (SQL)"

http://technet.microsoft.com/en-us/libr ... 88396.aspx

Trace Flags (Transact-SQL)

4616 - Makes server-level metadata visible to application roles...

Slawek

stryk

Slawek Guzek wrote:

http://technet.microsoft.com/en-us/library/ms188396.aspx

Trace Flags (Transact-SQL)

4616 - Makes server-level metadata visible to application roles...

Slawek

Exactly! And - among others - the SERVER STATE is one of this "meta data" which gets visible, thus it's like granting VIEW SERVER STATE permission ...

Slawek_Guzek

My point was that trace flag 4616 regards to application roles, not sever logins.

That's why in my opinion it is not equivalent or fully interchangeable with GRANT VIEW SERVER STATE to public.

More precisely - I would guess that GRANT VIEW SERVER STATE to public grants more wide permissions than trace flag 4616, so it should be possible to replace trace flag with it, but not other way around.. If NAV 2009 does not require T4616 anymore I would guess that it tries to get access to server metadata before it logins as application role, so T4616 is no longer enough, and GRANT VIEW SERVER STATE to public is necessary. Once granted it works also for application roles, so in effect T4616 is no longer required at all.. but GRANT VIEW SERVER STATE to public is.

DenSter

Slawek Guzek wrote:

I would guess that GRANT VIEW SERVER STATE to public grants more wide permissions than trace flag 4616

It's the other way around. Trace flag 4616 includes View Server State as well as other things.

ta5

Slawek Guzek wrote:

Does the VIEW SERVER STATE need to be granted manually?
Yes if you move or upgrade database. I am not sure in other cases like creating new databases - consult documentation. BTW for manual permissions use GRANT VIEW SERVER STATE TO [public].

Hi
Thanks for your answers. I don't understand why it may be used when moving/upgrading and it may not be used if creating a new db :?

Thanks in advance
Thomas

stryk

ta5 wrote:

I don't understand why it may be used when moving/upgrading and it may not be used if creating a new db :?

Thanks in advance
Thomas

Again: it should NOT be necessary to fiddle manually with VIEW SERVER STATE permission at all! Traceflag 4616 should be sufficient enough.

ta5

stryk wrote:

Again: it should NOT be necessary to fiddle manually with VIEW SERVER STATE permission at all! Traceflag 4616 should be sufficient enough.

Hi Jörg
So if I undertand you correctly, still use TF 4616 with Nav2009 (no matter SQL 2005/2008 or new or migrated db), altough officially TF4616 is not mentioned as a prerequisite anymore? Sorry, I'm a bit confused.
Thomas

DenSter

For NAV2009 the trace flag is not required, but you will need to grant view server state to public.

stryk

ta5 wrote:

stryk wrote:

Again: it should NOT be necessary to fiddle manually with VIEW SERVER STATE permission at all! Traceflag 4616 should be sufficient enough.

Hi Jörg
So if I undertand you correctly, still use TF 4616 with Nav2009 (no matter SQL 2005/2008 or new or migrated db), altough officially TF4616 is not mentioned as a prerequisite anymore? Sorry, I'm a bit confused.
Thomas

The TF 4616 is not required since NAV 2009 Service Pack 1 - the SP makes the difference! With older versions you still nee TF 4616.