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?