Or just turn off UAC.
If you’re getting a login failed for your Windows user – which you’re sure you put in the sysadmins role – it’s probably because UAC isn’t pasing all your group memberships to SSMS when you run it, and therefore giving you access denied. If you check your SQL errorlog and you see something like this:
Login failed for user Username Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.
It’s probably UAC. Try right clicking and running as administrator and seeing if it goes away. Of course if you added your user explicitly you’re probably fine, but just to get a cluster up and running I added my domain admins user to the DB – and of course that’s a membership that UAC will mask.
Yes, this was my issue for EventID 18456. I created a shortcut to ‘SQL Server Management Studio’ and configured the shortcut’s advanced properties to ‘Run as administrator’. Then it launched without issues.
OMG so simple… haha
Why doesn’t MS get smart enough to let users know this when they install or launch SQL 2008 on Server 2008?
To be fair, Microsoft best practice does state not to give Management Studio, or the SQL Server service account, elevated privileges:
http://technet.microsoft.com/en-us/library/cc512639.aspx
Yeah that’s fair enough, but I think that most of us who are logging into production SQL servers will be local admins, and therefore be running SSMS like that; if they’d made it UAC compliant it wouldn’t be an issue! This might be fixed in SQL 2008 R2, I haven’t checked.