I'm running VC 2.5u2 on a Win2k3SP2 box with SQL2k5SP2. I'd like to be able to monitor permission changes to VirtualCenter. The "permission created/modified/deleted" info event is only helpful if I actively look for it and it's still in the logs. Since the permissions seem to be stored in the VPX_ACCESS table, monitoring that seemed like a good idea. I tried the following:
CREATE TRIGGER VPX_ACCESS_TRIGGER
ON VPX_ACCESS
FOR UPDATE,INSERT,DELETE
AS
DECLARE @to varchar(50), @title varchar(100), @body varchar(1000)
SET @to = 'me@company.com'
SET @title = 'VirtualCenter permissions on ' + @@servername + ' have been modified!'
SET @body = 'SELECT * from VCDB.dbo.VPX_ACCESS'
EXEC msdb.dbo.sp_send_dbmail @recipients = @to, @subject = @title, @query = @body
In theory, that should email me whenever the table changes. But VirtualCenter shits itself whenever I try adding a test permission entry.
An unrecoverable problem has occurred, stopping the VMware VirtualCenter service.
Check database connectivity before restarting.
Error: Error[VdbODBCError] (-1) "ODBC error: (42000) - [Microsoft][SQL Native Client][SQL Server]
Error formatting query, probably invalid parameters" is returned when executing
SQL statement "INSERT INTO VPX_ACCESS (ID, PRINCIPAL, ROLE_ID, ENTITY_ID, FLAG) VALUES (?, ?, ?, ?, ?)".
Anyone else try something like this?
