Saturday, June 15, 2013

vCloud Director 5.1 upgrade and missing sysadmin permissions

I`ve recently managed to crash my test lab. One of the rebuild tasks was to reinstall vCloud Director - the hard way: dropping the DB and recreating it. Next I was running the vCloud Director installation script (5.1.1), recreating the DB schema and so on. After a week I`ve decided to upgrade the vCD installation from 5.1.1 to 5.1.2.
After downloading the bin file (vmware-vcloud-director-5.1.2-1068441.bin) from VMware site and uploading it on my vCD cell , I`ve executed the bin and a long list of "Yes" ends up with an error:

Unable to update database statistics. Database user has insufficient privileges to update database statistics. To complete this step manually, run 'EXEC sp_updatestats' as the DBO or a member of the sysadmin fixed server role.

Next, I`ve been looking in the DB server (MSSQL) - logging in with vCD DB admin user (vcddbadmin) and checking if the rights are ok. And they were, since the user was dbowner. I`ve ran 'EXEC sp_updatestats' in query editor and ended up with another error:

Msg 15247, Level 16, State 1, Procedure sp_updatestats, Line 15
User does not have permission to perform this action.

It was stored procedure time: vcddb01 -> Programmability ->Stored Procedures -> System Store Procedures and after a few pages of scrolling down: sys.sp_updatestats. I`ve opened the stored procedure (Context menu - Modify) and on line 15 there was a check to see if the current user was sysadmin. Which, obviously it was not.

Below, a small SQL script that will display if the current user is sysadmin or not:
if is_srvrolemember('sysadmin') = 1
print  ' It is sys admin'
else if is_srvrolemember('sysadmin') = 0
print  ' It is NOT sys admin'

To solve the problem, I`ve added sysadmin role to the vCloud Director DB admin user (vcddbadmin) and ran again the stored procedure:

This time it worked ok and updated database statistics. I`ve checked back the installation procedure for vCloud DB and I`ve taken a look at KB 2034540 to see if I`veforgotten anything while creating the user for vCD DB. But it seems I did not. The only reference is to db_owner role. Since I am not a DB guy, I`ll just leave it as is, happy to have solved my problem.