Snippet Name: mysschema

Tags: helpful,scriptgeneration

Created Date: Apr 27 2022 8:50AM
Last Modified Date: Apr 27 2022 8:50AM

–desc: linked server best practices settings

Dependencies: none-standalone code

Sample Output:

 
 --desc: linked server best practices settings
 
 SELECT
 
   CASE WHEN svrz.[is_remote_login_enabled] = 0 THEN 'EXEC master.dbo.sp_serveroption @server=N''' + name + ''', @optname=N''rpc'', @optvalue=N''true'';' ELSE '' END + CHAR(13) + CHAR(10)
 
 + CASE WHEN svrz.[is_rpc_out_enabled] = 0 THEN 'EXEC master.dbo.sp_serveroption @server=N''' + name + ''', @optname=N''rpc out'', @optvalue=N''true'';' ELSE '' END + CHAR(13) + CHAR(10)
 
 + CASE WHEN svrz.[is_remote_proc_transaction_promotion_enabled] = 1 THEN 'EXEC master.dbo.sp_serveroption @server=N''' + name + ''', @optname=N''remote proc transaction promotion'', @optvalue=N''false'';' ELSE '' END + CHAR(13) + CHAR(10)
 
 + CASE WHEN svrz.[is_data_access_enabled] = 0 THEN 'EXEC master.dbo.sp_serveroption @server=N''' + name + ''', @optname=N''data access'', @optvalue=N''true'';' ELSE '' END + CHAR(13) + CHAR(10) AS LinkedBestPractices,
 
 * 
 
 
 
 FROM sys.servers svrz
 
 WHERE svrz.[is_linked] = 1
 
 AND (svrz.[is_rpc_out_enabled] = 0
 
 OR svrz.[is_remote_login_enabled] = 0
 
 OR svrz.[is_remote_proc_transaction_promotion_enabled] =1
 
 OR svrz.[is_data_access_enabled] = 0)