2

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?

  • This might just be a stupid permissions issue. I am running as SA but for some reason I cannot right click and add a server role under security. The option is not available in the context menu. Perhaps it is a bug though... dba.stackexchange.com/questions/126570/… – Matt Mar 22 '17 at 12:46
  • Why are you worried about adding the permissions to the role if you only need them temporarily and just for upgrade process? There is no way to grant server level privileges to a role in SQL Server 2008 R2. – Shawn Melton Mar 22 '17 at 13:12
  • If I had a role then the next upgrade, in theory, I would not have to worry/look up what the individual permissions that would be needed. I was viewing this a learning experience if nothing else. For the time being I am doing exactly what you said. I has assumed there was another way though. There is no way to grant server level privileges to a role in SQL Server 2008 R2. If you have anything authoritative to that effect then that would be my answer. I cannot find a reference for that yet. – Matt Mar 22 '17 at 13:15
3

Just because you create a role in the master database doesn't make it a server level role. It's still a database role - it just lives in a really important database.

SQL Server 2008R2 simply didn't have user defined server level roles. For that feature, you'll need to upgrade to SQL Server 2012.

You asked for something authoritative, and the best answer there is good ol' Books Online, where they list what's new in SQ Server 2012 security features:

https://technet.microsoft.com/en-us/library/42919d6c-1fd4-466e-ac75-d579248c07df(v=sql.110)

  • So I am not crazy then? Just what I want is not possible in my given environment? – Matt Mar 22 '17 at 13:26
  • 2
    "Am I not crazy" is an opinion question. Here at Stack, we focus on facts. Your title was about "can I" not "should I." If you want a separate opinion based question, you're welcome to add that - just be careful how you word it. You may not wanna hear the answer if you ask strangers if you're crazy. ;-) – Brent Ozar Mar 22 '17 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.