xp_cmdshell raises error "a call to LogonUserW failed with error code 1385"

xp_cmdshell requires a couple of permissions to enable non-sysadmin users to run it, and even when you think you have enabled it in surface area config, and assigned an account to the ##xp_cmdshell_proxy_account##, you will frequently find you still encounter the following error:

An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1385'.

In order to fix this you need to open the Local Security Settings on the host machine.
 
Navigate to Security Settings -> Local Policies -> User Rights Assignment.
 
Now open "Log on as a batch job" and add the user you assigned to the xp_cmdshell proxy account - and you should find it runs fine now.

Error using linked server from 64bit to 32bit SQL

A colleague came across an error using a linked server connection to retrieve data from another server, which gave an error claming:

The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator. Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "". The provider supports the interface, but returns a failure code when it is used.

We knew the table did exist, and the user had permissions.  The same statement also worked from another server, so it wasn't the syntax either.  We assumed it was some issue going from SQL 2008 to SQL 2000, but it seems there is a small issue with using linked servers between 64-bit and 32-bit versions due to the way it queries meta data.

The Microsoft line on fixing this is "To resolve this problem, manually run the Instcat.sql script that is included with SQL Server SP" - which begs the question, why doesn't the SP do this automatically?!

Our fix though was to manually create "sp_tables_info_rowset_64" which the 64-bit server tries to run on the 32-bit box.  There are several blogs that script out the full code for doing this, but this code is almost exactly the same as the proc that already exists on the 32-bit version, namely "sp_tables_info_rowset" - so we found the quickest and tidiest solution to be just to use that, as follows:

USE master GO CREATE PROCEDURE sp_tables_info_rowset_64 @table_name sysname, @table_schema sysname = null, @table_type nvarchar(255) = null AS DECLARE @Result INT SET @Result = 0 EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type GO GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC

You should now find the error magically goes away.