When I created datasets via Enterprise Studio against a SQL Server 2000 database I didn't create the datasets using the "SA" user. I instead created via the dataset wizard using a user such as "trimuser". Therefore in SQL2000 now I have say 3 databases that are my datasets and they all have the same prefix to all their objects of "trimuser". No big deal really as that's how Oracle has done it since forever.
The reason why I'm relaying all of this is that as of SQL Server 2005 there is now the concept of a schema in SQL Server being different than the owner. I want to move my datasets from SQL Server 2000 to SQL Server 2008 under Trim 7.1. But I'm curious if they should have the prefix now of DBO. This seems to be the best thing to do in SQL Server and is the default for a new user in SQL Server.
It seems that back when I created Trim 6.24 datasets in SQL Server 2000 that SQL Server 2000 when I created using the "trimuser" user that the objects got the "trimuser" prefix instead of "dbo"
If I really think it's worth messing with this I have a few options. Changing the owner after I restore the datasets to SQL Server 2008. The other option is changing the owner on the SQL Server side before the backup and restore.
Curious what others do when they create datasets in SQL Server. You use the DBO schema or the schema for a dedicated Trim user? I know you guys ain't using SA to connect to your dataset as that's not best practice but maybe you use it for dataset creation only.
I think the key thing here to note is the account with which you run TRIM Enterprise Studio with, when using Windows Authentication. Users in SQL Server have a default schema, and actions performed will use this schema. If, for example, your database had the schema of 'trimuser' and you run TES with an account that defaults to the dbo schema, you can run into problems. Namely, a Schema Repair will likely create blank tables and new procedures & views in the dbo schema. If the schema happens to have an alphabetic name that comes before 'trimuser' like dbo would, TRIM would then not work. This is because when starting the Workgroup server, it will look at dbo.TSTWRSYS which would be a blank table. TRIM would never find trimuser.TSTWRSYS.
Now that you've changed them, you should be fine granted any users running TES will default to the same schema. It's because of this behavior that in some environments, a SQL account is used instead which will always use the same schema. The password is encrypted, as seen in the Connection String, and the account can be specific to the TRIM database. It does not have to be SA.
Windows Authentication I've always wanted to do as then no need to create a SQL Server user. However if I remember right Windows Authentication has some drawbacks. The account you end up running with is the account used for the Trim Windows services if I remember right.
How I've always seen things is that when a schema is created and you get prompted for the connection string you are picking what will be used to create the objects as well as the user that Trim uses to connect to the database. That is why I picked a user called "trimuser" for instance which is a SQL Server user. I think maybe I should change my way of thinking.
Maybe in the future I will create the dataset with either SA or a user whose default schema is DBO. Of course since my datasets in question were in SQL Server 2000 they got the schema "trimuser" since in SQL Server 2000 the owner and schema are the same thing. In SQL Server 2008 I created a new user for example called "trimdbuser". By default SQL Server 2008 gives this user the schema DBO. Big difference. When I create a brand new dataset therefore under Trim 7 in SQL Server 2008 the schema they go under is DBO instead of "trimdbuser" like what would have happened under SQL Server 2000.
One thing I had to do was that I had to give the user the db_owner SQL Server role after the objects were changed to the DBO schema. When I tried to register the dataset on a new server Trim complained about not finding one of the tables. So I guessed that the reason was that Trim was looking for table names with a schema of "trimdbuser" since that was the user I was connecting with. After granting that SQL server teh db_owner role, which I believe allows SQL Server users to create objects under the DBO schema, Trim now could find the tables. Followed the help and upgraded my schemas, fixed the email search keys and will now recreate my content indexes.