I routinely use SQL Server Management Studio to write SQL queries, ogle details of the database schema, etc. Last week, I needed to change the permissions on a SQL login, and I got the following message:
That confused me, as I was logging in to SQL using Windows authentication, and I am a local admin on my machine; as such I should be a member of BUILTINAdministrators – which is by default configured to be a member of the sysadmin server role in SQL Server. But I kept getting "Access denied" messages for every admin-type action.
What finally tipped me off is that I was executing the "sp_who" stored procedure to take a look at all of the connections to the SQL instance, and the only result that was returned was my own connection from SQL Management Studio. I knew that wasn’t right, as I had other applications running that were successfully connecting to SQL Server and consuming data from it.
So I tried the old Vista standby: I ran SQL Server Management Studio as administrator. And suddenly it all worked.
Of course, after going through this exercise, I found the TechNet article that describes how Vista’s user account control (UAC) inhibits the "typical" functioning of the BUILTINAdministrators group. The recommended best practice is to explicitly grant membership to users for the necessary server roles (e.g. sysadmin).
I’m sure that’s good advice. And I figured if I blogged about it, I’ll have an outside chance at remembering it next time this particular issue bites me.
