SQL Server 2005 schemas and security


We will be looking to upgrade to SQL Server 2005 in the near future, so we have been testing in our development environment. On our old 200 Server platform the security aspects are pretty simple. We use the standard security model so at a SQL Server level we grant the user the public role and Navision deals with the rest. With SQL Server 2005 this seems to be a lot more complicated with the introduction of schemas. As far as I understand it, the schema contains the security settings, so you can configure a scheme with the relevant permissions and then re-use the schema for any additional logins. So, when I now create a new user login it also seems to create a schema for the user. If I make the new login a db_owner (e.g. for users who need to run the synchronise security option) they will also receive the DBO schema for this database. So, what do people do now do for schemas. Create one and assign all non-admin users to it, or create one for every login? This would seem to defeat the purpose of schemas, but as far as I can see, my schemas don’t contain any explicit permissions anyway?

I suppose the one thing I simply do not get is how schemas and roles relate (like e.g. does one overrule the other?), anybody here who can help me to fill in the blanks or point me in the direction of an MBS white paper that can assist?

Also am I correct in thinking that trace flag 4616 is required for SQL server 2005 to work correctly?

And finally, when you restore an existing 2000 DB the compatibility mode is not automatically set to version 9.0, is this required and why? What happens if you don’t?

Many thanks


Schema creation is transparent when you assign users to Navision. You must only create a User with public role and Navision will do the rest. I recommend maintaining in standard security.
I don’t think there is a white paper about SQL about Schema in SQL Server.
Trace flag 41616 is required by Navision client not SQL Server.

When you make a technical upgrade I like to restore DB from Navision client. In this way compatibility will be changed to 2005 and will not be maintained in SQL 2000.

Also check this http://dynamicsuser.net/forums/p/19762/90363.aspx