Upgrade without being SQL admin

[clabough]

Is there a way to perform an upgrade from Vault 5.01 to 5.03 without being sa or a member of the SQL sysadmin role? I have the sgvaultuser credentials that is dbowner, but the install script will not finish without being a sysadmin. I'm getting error -1280.

Our IT policies will not allow for application scripts to be ran under that role, so I'm looking for another way to do the upgrade.


=====
Connecting to the SQL Server...ConnectToDatabase() completed successfully.
dialog result for uninstall is: OK
OK
Verifying the SQL Server requirements... User is trying to install to Microsoft SQL version 9.00.4035.00
OK
Checking for existing databases...Found.
Asking for database's fate...Keep existing.
Upgrading the existing source control database...
This may take a long time. Do NOT stop this process!
Checking the installed database version... 5.0.0.6
OK
Putting sgvault database in single-user mode.
getting the collation of the current source control database
the Vault database has the collation SQL_Latin1_General_CP1_CI_AS
Executing commands in master_ddl.sql
Read master_ddl.sql from our resources.
substituting collation for master_ddl.sqlwith collation SQL_Latin1_General_CP1_CI_AS
Creating SourceGear master database schema...Error executing SQL command in master_ddl.sql-------
CREATE DATABASE [sgmaster] COLLATE SQL_Latin1_General_CP1_CI_AS
--------
CREATE DATABASE permission denied in database 'master'.
Vault Setup is exiting due to a failure or cancellation. Error Code = -1280
CREATE DATABASE permission denied in database 'master'.
Vault Setup is exiting due to a failure or cancellation. Error Code = -1280ExitCode on exit: -1280

[Beth]

The person performing the upgrade needs to have SQL admin rights. The account that Vault creates, sgvaultuser, is not a SQL admin and cannot perform the upgrade. The SQL admin is not used during normal Vault use, only installs and upgrades. It doesn't have to be the actual sa account. You can create a new SQL user that has administrator rights for the purpose of the upgrade and then delete or demote that user when the upgrade is done.

[clabough]

I realize it doesn't need to be the SA account - hence why I was referring to the role. Since this is an upgrade, and the database and account already exists that it's going to use, why not put an option in the installer to skip those commands and continue on? This would allow an account that is dbowner to perform an upgrade.

It's hard to believe that this isn't an issue for many others. Giving one application elevated privledges so that it could potentially see into every other database on the machine is against security best practices.

Is there any way to do the upgrade manually with a dbowner role account?

[Beth]

There currently is no work-around for on that machine. I can add a feature request for this functionality though.

One thing you can do is perform the upgrade with the database in a different location and then move the database back. It's a messy way to do it, but it would work.
1) Detach the database.
2) Move the database to a different SQL Server that you have access to or some other instance, and attach it there.
3) Run the upgrade on the production server, but when it gets to the SQL portion, you will point to the new SQL database location.
4) Detach the database from the new location, move it back to the original location and attach it.
5) Change the SQL connection in the web.config file to point to the original location.
6) Set permissions again using the SQL scripts in this KB article: http://support.sourcegear.com/viewtopic.php?t=924

[clabough]

Thanks, What I had to do was a bit more than that for my case.

- I had the SQL Administrator do a backup of sgmaster and sgvault, then restored those into a local machine.
- The existing web server could not connect to this database, so I installed Vault server onto the local machine and had it update the databases.
- Backed up the new databases and had those restored over the top of the old databases on the central server.
- Copied the VaultService folder from the local machine to the central web server
- Edited vault.config, web.config, and index.html with the central server's link addresses

Things seem to be working ok so far (just completed 30 min ago).

A feature request would be good for allowing non-admins to perform an update.

[Beth]

I have a request for that, which I can add your "vote" to. Thank you for your feedback.

F: 6392