My server admin asked me to grant a user SA privileges for the purpose of upgrading our vCenter environment from 5.5 to 6.x and I refused. So I found a VMWare article that outlined all of the required permissions and set the up in a role so that I could assign the vCenter database user to the role for the purpose of upgrading and then remove the user from the role once complete. That part worked correctly with some cosmetic name changes to match our current environment. However the upgrade still failed with the following error:
Error : The user associated with the DSN has insufficient privileges. Resolution: Provide sufficient privileges for the user by granting the user the following permissions: * VIEW SERVER STATE * VIEW ANY DEFINITION
OK so these are server level permissions. That is fine. So I wanted to add these privileges to the role I created for this "vc_admin".
USE master GRANT VIEW SERVER STATE to vc_admin Grant VIEW ANY definition to vc_admin GO
Which netted me this set of errors.
Msg 15151, Level 16, State 1, Line 3 Cannot find the login 'vc_admin', because it does not exist or you do not eave permission. Msg 15151, Level 16, State 1, Line 4 Cannot find the login 'vc_admin', because it does not exist or you do not have permission.
So I add the role to the master database
CREATE ROLE vc_admin successfully. The code above is run again and I get the same errors again.
If I change the code snippet above to target the user instead of the role it completes successfully. I suspect my issue is a simple misconception.
I read that I might have to create a server role for this to work which would explain the error. I tried the following which SSMS thinks is incomplete / syntactically incorrect.
create server role vc_admin
Now according to msdn the authorization is optional and adding that didn't change the error.
Expecting ID, Quoted ID or TO
I read now that ...
Server-scoped roles were introduced on SQL Server 2012, they are not supported in SQL Server 2008R2.
So I that train of thought might be a goose chase for me.
How can I get these privileges assigned to my vc_admin role instead?