SQL Login Failed: Token-based server access validation failed with an infrastructure error

Something I'd forgotten when I was setting up a new SQL server, was how the UAC (User Account Controls) permissions affect connections to SQL when the windows account in question is part of a group.

I've only experienced this with sysadmin accounts, but the problem can occur in applications or even Management Studio, where users who should have full sysadmin access find they cannot connect at all.

The only clue is that the SQL log contains the lines:

Source: Logon
Message: Error: 18456, Severity: 14, State: 11.

and

Source: Logon
Message: Login failed for user 'DOMAIN\grumpydba'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 1.2.3.4]

Issue is that SQL doesn't seem to apply sysadmin access rights to users / accounts that only exist in SQL groups.

Two quick fixes:

1. If you are opening SQL Management Studio, right click and select "Run as administrator".  This should give you the access you expect.

2. Add the account explicitly as a SQL login, and grant sysadmin.  (Use this if for a system account) This will give the account the relevant permissions and able to connect.

SQL Service Pack Versions

I know this information is available in multiple other places, but I can never find it when I need it, so I'm putting it here for my benefit ... and for anyone who stumbles across this!

First up, it is possible to determine the service pack level directly from SQL using the SERVERPROPERTY function with the 'productlevel' key, for example:

SELECT @@SERVERNAME AS "SQLInstance" ,

SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS "Server",

SERVERPROPERTY ('ProductVersion') AS "Build",

SERVERPROPERTY ('edition') AS "Edition",

SERVERPROPERTY ('productlevel') AS "Patch"

would return results similar to

SQLInstance	Server	Build	Edition	Patch
GRUMPY\DBA	GRUMPYCLUSTER1	10.50.4000.0	Enterprise Edition (64-bit)	SP2

Loop through all your servers and you can quickly keep track of which servers are at what build, and what needs patching.

However, if you just want to check what patch level a certain build number equates to (since you can see that as soon as you connect in SSMS), here are the major releases and their version numbers:

SQL 2000	Shiloh
8.00.194	RTM
8.00.384	SP1
8.00.534	SP2
8.00.760	SP3
8.00.2039	SP4
SQL 2005	Yukon
9.00.1399	RTM
9.00.2047	SP1
9.00.3042	SP2
9.00.4035	SP3
9.00.5000	SP4
SQL 2008	Katmai
10.00.1600	RTM
10.00.2531	SP1
10.00.4000	SP2
10.00.5500	SP3
10.00.6000	SP4
SQL 2008 R2	Kilimanjaro
10.50.1600	RTM
10.50.2500	SP1
10.50.4000	SP2
10.50.6000	SP3
SQL 2012	Denali
11.00.2100	RTM
11.00.3000	SP1
11.00.5058	SP2
11.00.6020	SP3
SQL 2014	Hekaton
12.00.2000	RTM
12.00.4050	SP1 (original)
12.00.4100	SP1 (re-released)
SQL 2016
13.00.200	CTP

Refresh SQL Intellisense cache

It may not be as good as Red-Gates SQLPrompt, but I have to admit I'm getting used to SQL 2008's Intellisense.

What I was starting to get fed up with though was creating new objects or fields, and finding them underlined by intellisense who refused to acknowledge that I was correct.

I figured it must have an internal cache, but I was always too lazy to hunt around to find out how to refresh it - until now.
To be honest, it wasn't that tricky and there are mouse click or keyboard shortcut methods.

Keyboard method
In the query window with the offending underlined object - press Ctrl + Shift + R.

Mouse method
Take your hand off the mouse, put a finger on a shift key, a Ctrl key, and the R key, and press down!!!
In fact you could keep your right hand on the mouse, and press the keys with your left hand.
However, if you really want to increase your chances of RSI, and insist of doing everything through slow and cumbersome graphical interface menus, far be it from me to get grumpy (too late), go up to the Edit menu in SSMS, and under "Intellisense", you will find "Refresh Local Cache" for you to click away on to your hearts content.

It even tells you there though just to press Ctrl + Shift + R, so just do that OK?!!

Assign shortcut key to "change connection" in SQL MMS

Fed up having to click the "Change Connection Icon" every time you want to change your connection in management studio, and wish there was a shortcut key you could press?  Then worry no more (and try get out more often)!

SQL 2008 Management Tools install fails claiming Express Tools already installed

Quite a few users have had problems installing the SQL 2008 management tools - with an error message saying:

The SQL Server 2005 Express Tools are installed.  To continue, remove the SQL Server 2005 Express Tools.

After lots of playing around, uninstalling/reinstalling, it seems the issue is related to Red Gate tools, such as SQL Prompt and SQL Compare.

First try and uninstall any Red Gate tools you have installed and see if that fixes the issue.

If it still fails after uninstalling them, you may have to search the registry for any orphaned keys relating to the Red Gate tools

It looks like these Red Gate keys go under SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM - so if you still have these keys after uninstalling SQL Prompt, manually deleting these should fix the problem.

Include headers when copying results from SQL Management Studio

A handy little option to enable in Management Studio for users who copy and paste query results from the grid into Excel or other applications, is to include the headers as well as the data.

This can be done from the "Tools" -> "Options..." window.

Expand Query Results, then SQL Server, and finally select Results to Grid.

On the right you should now see the option to "Include column headers when copying or saving the results" - enable this and no longer will you have to type the headers into the spreadsheet again!

SQL 2008 Management Studio shows filename on tab

Another frequent complaint from users moving to the SQL 2008 Management Studio is that the text on the tabs now shows the filename rather than the server and database.

Don't panic!  This can be changed back!!

Simply select the Tools menu, and select "Options..."

Now in the left pane expand the "Text Editor" and select "Editor Tab and Status Bar". 

Finally scroll the right pane down to the bottom so you can see the "Tab Text" heading, and under that you can select what you want included on the tabs.

Cannot change table in SQL 2008 Management Studio

As more users are upgrading to SQL 2008, they are running into some of the small changes in the 2008 management studio.

One of these is an error message that pops up when a user tries to change a table via management studio design view by changing/deleting a column or adding a new column in the middle of the table.  Instead of saving the change, the user gets the following error:

This may fool you or the user into believing there is a permissions issue preventing the table being updated, but it's actually a setting in SSMS - presumably to prevent the application hanging if the table involved was of any significant size.

The fix though is to simply uncheck box in the Options window. 

Select "Tools" from the SSMS menu bar, then "Options..." from the drop down list. 

Expand the "Designers" section, and under the "Table and Database Designers" section, you should find the "Prevent saving changes that require table re-creation" option, which you can disable.

Or you could just learn how to use ALTER TABLE statements in T-SQL!

SQL 2008 - Where did Surface Area Configuration go?!

No sooner had we got used to opening the oddly titled "Surface Area Configuration" tool to change certain options on SQL 2005 servers that used to be on the properties tab in SQL 2000 (e.g. enabling xp_cmdshell), than it has moved again.

SQL 2008 brings us a new description of these items, and we must now call them a "Facet" ... and no, that's not another name for a tap.

However, on the plus side, you can now access these through the 2008 Management Studio.  Simply right click on your server name in the object explorer pane, and select "Facets" from the context menu.

This brings up the "View Facets" display.  From here you can select "Surface Area Configuration" from the Facet drop down menu, and enable or disable your required options directly from here.

The other good thing ... this also works for SQL 2005 servers, whereas alternative methods for doing this (e.g. Policies) don't.

Add your own Management Studio Shortcut Keys

One of the things I like about SQL Server Management Studio (SSMS) is being able to customise some of the short cut keys - but it's one of those things that when someone sees it, and asks how you set it up, it's a pain to remember!

So for those of you who have a new installation and have forgotten how to set up a short cut for your favourite proc, or those new to this keyboard shortcut feature, here is how you do it.

Select "Options" under the "Tools" menu on the SSMS menu bar - and just because I can, I'll even show this to you:

With that selected, a new window titled "Options" will open.
In here expand "Environment" and then "Keyboard" and you should see the right side of the window display "Query shortcuts".

Now you can enter procs against the available Ctrl+x shortcuts - note that a few are fixed (like Alt+F1 as sp_help), but there are 7 or 8 that you can assign a proc against.

Note that these run in the context of the database you are connected to - so unless the proc you create or assign is an "sp_" prefixed proc that exists in the master database, it must be in the database you are connected to.

Once this is done, click OK, and open a new query window (won't work for windows already open as far as I remember).  Now depending on whether the proc you assigned needs parameters or not, you can simply press your shortcut key (e.g. Ctrl+3) to run it instantly, or highlight the relevant parameters (e.g. a table name) and hit the shortcut key.