Linked Server – commands, one hop

Easy linked server setup for a one hop query.

PC (A) to SERVER (B) will
work.
PC (A) client tools to SERVER (B) and four-part queries from
SERVER (B) connection to SERVER (C) will not work without some kind of
delegation.

See "How to set up a Kerberos Authentication Scenario with SQL Server Linked Servers" and this "Allow a computer to be trusted for delegation for specific services"

-- Add a linked server
EXEC sp_addlinkedserver 
@server = N'SQLDEV',
@srvproduct  = N'SQL Server'
GO
-- And some options
exec sp_serveroption 
@server = N'SQLDEV', 
@optname = N'data access', 
@optvalue = N'true' 
exec sp_serveroption N'SQLDEV', N'collation compatible', N'true' 
GO
EXEC sp_addlinkedsrvlogin 
@rmtsrvname = 'SQLDEV'
,@useself = 'TRUE'
--     ,@locallogin = 'sa'    -- this doesn't seem to be required.
GO
EXEC sp_helpserver 'SQLDEV'
EXEC sp_helplinkedsrvlogin 'SQLDEV'
GO
-- This query will work from one hop. e.g. If you are querying directly from one local server to another.
-- You can't do this by Query Analyser on PC A, logging into server B, and running the 4 part query agains
-- Server C.
select * from sqldev.master.dbo.sysservers
GO
-- Some options
-- exec sp_serveroption N'SQLDEV', N'query timeout', 0 
-- exec sp_serveroption N'SQLDEV', N'collation name', N'null' 
-- exec sp_serveroption N'SQLDEV', N'use remote collation', N'true' 
-- exec sp_serveroption N'SQLDEV', N'rpc', N'true' 
-- exec sp_serveroption N'SQLDEV', N'rpc out', N'true' 
-- exec sp_serveroption N'SQLDEV', N'data access', N'true' 
-- exec sp_serveroption N'SQLDEV', N'collation compatible', N'true' 
-- exec sp_helplinkedsrvlogin N'SQLDEV'
-- exec sp_addlinkedserver N'SQLDEV' 
-- Clean up our test
--  Exec Sp_dropserver 'SQLDEV', droplogins