SQL Server Project (5)

I have written several blogs on SQL Server Projects. Here is the list of links of previous posts:

• SQL Server Project 1


• SQL Server Project 2


• SQL Server Project 3


• SQL Server Project 4

Recently we moved our SQL server from one virtual machine to a real PC SQL server. DBAs created the new server and installed a blank SQL server. After that, we had to move our DBs to the new user.

Generally, DBAs don't give us sa password. What they did is to created an appsupport user with almost as same as settings as sa for us. We use this user to back up the existing DBs and copied to the new PC for restoring.

The restoring process went very well. Almost every thing has been set up as same as the existing DBs. However, for SQL database project, as I mentioned in previous articles, some Asymetric Keks for assembly keys and Log Permissions have to be set up in master db. Fortunately, I logged the steps to do that (as I wrote in my previous articles) and restored the keys and permissions in master DB.

One thing I realized that the master DB as created by sa and our other DBs with SQL database projects were restored by appsupport. The owners of these DBs are different. As a result, I could not deploy my project to DBs. The error messages say that the owners are different and they could be changed by ALTER... I used this command to change the DB(MyDB for example) owner:

  ALERT AUTHORIZATION DATBASE::MyDB TO sa

I think that if DBs' owners are different, you cannot deploy your project from Visual Studio 2005. You have to make change to keep them in sync.

The second thing I had to do is to set DB TRUSTWORTH on:

  ALTER DATABASE MyDB SET TRUSTWORTHY ON

since I referenced System.Web.dll framework assembly in my SQL database project. Otherwise an exception would be thrown out when Ssytem.Web.dll classes are called.

The last thing is to give permissions to Windows' users since I have to use VS to deploy my project remotely to the SQL server with Windows authentication. Instead of give permissions(alter and create assembly) to one user, I created a Role on SQL server DB called as db_developers and adding my user name as a member of the role. Then I set related permissions to the role.

After all these settings, I can deply my project remotely to the SQL server DB and run the deployed assembly based ST with success.