SQL Server SQL script to create operator

USE [msdb]

GO
EXEC msdb.dbo.sp_add_operator @name=N’OperatorName’,

@enabled=1,

@weekday_pager_start_time=90000,

@weekday_pager_end_time=180000,

@saturday_pager_start_time=90000,

@saturday_pager_end_time=180000,

@sunday_pager_start_time=90000,

@sunday_pager_end_time=180000,

@pager_days=0,

@email_address=N’email1@abc.com; email2@abc.com; email3@abc.com; email4@abc.com; email5@abc.com;’,

@category_name=N'[Uncategorized]’

GO

Advertisements

SQL Server Email Overview

Excellent Reference

 

Database Mail in Microsoft SQL Server is a powerful, but under-utilized ally for any Database Administrator. It’s been around for years but often goes unnoticed to this day. We rely upon Database Mail (let’s just call it DBM through the remainder of the article) to field such tasks as:

  • Notifying DBAs when SQL Server Agent jobs complete, fail, or succeed
  • Sending the results of queries via email to interested individuals
  • Sending alerts when certain conditions arise on the SQL Server instances

If any of those things sound like functionality you’d like to implement then this is the article you’ll want to read.

Where is Database Mail in SQL Server Management Studio

DBM is located in the SQL Server Agent node of the Object Explorer window:

Double-clicking Database Mail will launch a process to step you through creating a DBM profile and an account to associate it with.  It’s important before we go any farther that we clear up some terminology though between accounts and profiles.

Database Mail Accounts are closely associated to an SMTP (Simple Mail Transfer Protocol) account. The DBM account is essentially the information stored about the mail server, the security process to connect to the mail server, and how email sender in this process will be identified to recipients. You need to supply the following criteria when setting up a DBM account:

  • Account Name
  • Account Description
  • Outgoing SMTP Mail Server Information:
    •  Email Address
    • Display Name
    • Reply Email Address
    • SMTP Server Name (IP or DNS name are acceptable)
    • Port Number
    • SSL information
  • SMTP Authentication Method (Windows, Basic, or Anonymous)

Database Mail Profiles are no more than identifiers for SQL Server to use to associate a request (the what) to the logistical information for the delivery (the how). All that is required is a name to identify the profile, an optional description for the profile, and then the account name to link to the profile. Multiple accounts can be associated with a given profile and if the first account fails to send a transmission the next account will be used.

Now that you know the information you’ll need to create DBM Profiles and Accounts we want to move on to the script we will run to do this all in one query.

 

The script is broken down into the following sections:

  • Creation of Database Mail Account
  • Creation of Database Mail Profile
  • Enable Database Mail for the SQL Server instance
  • Returning list of accounts and profiles for the SQL Server instance
  • Send test Database Mail
  • Assignment of a Database Mail profile to the SQL Server Agent

The GUI process in SQL Server Management Studio does not cover enabling Database Mail on the instance nor assigning a profile to SQL Server Agent. The script saves you the work of having to set these in the server’s Facets and in the SQL Server Agent configuration respectively.

The Query

Now for what you’ve been reading on until the end for: the do-it-all script.

--================================================================
-- DATABASE MAIL CONFIGURATION
--================================================================
--==========================================================
-- Create a Database Mail account
--==========================================================
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = '<account_name, dbm="" account="" name,="" database="" mail="" primary="">',
    @description = '<description, ,="" sql="" server="" notification="" service="">',
    @email_address = '<email_address, email="" address="" for="" dbm.="" does="" not="" need="" a="" valid="" mail="" account="" ,="">',
    @replyto_address = '<replyto_address, reply="" email="" address="" for="" dbm.="" does="" not="" need="" a="" valid="" mail="" account="" ,="">',
    @display_name = '<display_name, friendly="" name="" for="" emails="" sent="" via="" dbm,="" database="" mail="" account="">',
    @mailserver_name = '<mailserver_name, smtp="" mail="" server="" name,="">',
	@port = <port_number, port="" number="" of="" the="" mailserver,="" 25="">;

--==========================================================
-- Create a Database Mail Profile
--==========================================================
DECLARE @profile_id INT, @profile_description sysname;
SELECT @profile_id = COALESCE(MAX(profile_id),1) FROM msdb.dbo.sysmail_profile
SELECT @profile_description = 'Database Mail Profile for ' + @@servername 


EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = '<profile_name, dbm="" profile="" name,="" database="" mail="" primary="">',
    @description = @profile_description;

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = '<profile_name, dbm="" profile="" name,="" database="" mail="" primary="">',
    @account_name = '<account_name, dbm="" account="" name,="" database="" mail="" primary="">',
    @sequence_number = @profile_id;

-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = '<profile_name, dbm="" profile="" name,="" database="" mail="" primary="">',
    @principal_id = 0,
    @is_default = 1 ;


--==========================================================
-- Enable Database Mail
--==========================================================
USE master;
GO

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO 


--EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N''
--EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
--GO

EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder = 0
GO


--==========================================================
-- Review Outcomes
--==========================================================
SELECT * FROM msdb.dbo.sysmail_profile;
SELECT * FROM msdb.dbo.sysmail_account;
GO


--==========================================================
-- Test Database Mail
--==========================================================
DECLARE @sub VARCHAR(100)
DECLARE @body_text NVARCHAR(MAX)
SELECT @sub = 'Test from New SQL install on ' + @@servername
SELECT @body_text = N'This is a test of Database Mail.' + CHAR(13) + CHAR(13) + 'SQL Server Version Info: ' + CAST(@@version AS VARCHAR(500))

EXEC msdb.dbo.[sp_send_dbmail] 
    @profile_name = '<profile_name, dbm="" profile="" name,="" database="" mail="" primary="">'
  , @recipients = '<test_email_address, email="" address="" to="" send="" test="" email,="">'
  , @subject = @sub
  , @body = @body_text

--================================================================
-- SQL Agent Properties Configuration
--================================================================
EXEC msdb.dbo.sp_set_sqlagent_properties 
	@databasemail_profile = '<profile_name, dbm="" profile="" name,="" database="" mail="" primary="">'
	, @use_databasemail=1
GO</profile_name,></test_email_address,></profile_name,></profile_name,></account_name,></profile_name,></profile_name,></port_number,></mailserver_name,></display_name,></replyto_address,></email_address,></description,></account_name,>

SQL Server SQL To Create Proxy

USE [msdb]

GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N’Proxy Name’,@credential_name=N’Cred Name’,  @enabled=1

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’Proxy Name’, @subsystem_id=3

GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’Proxy Name’, @subsystem_id=9

GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’Proxy Name’, @subsystem_id=10

GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’Proxy Name’, @subsystem_id=11

GO
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N’Proxy Name’, @login_name=N’Login name 1′

GO
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N’Proxy Name’, @login_name=N’Login name 2′

GO
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N’Proxy Name’, @login_name=N’Login name 3′

GO

SQL Server linked server to CSV file gives error The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229) due to lack of EXECUTE permissions ON SYS.XP_PROP_OLEDB_PROVIDER

We had created the linked server on SQL server to consume CSV files.

You can get details in this post.

The user could consume the files using query:

select * from [TEST-CSV]…[FileName#csv]

Though in object explorer of SQl management studio when try try to browse they use to get error:

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229)

The solution was to grant EXECUTE permissions ON SYS.XP_PROP_OLEDB_PROVIDER using:

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [DomainName\UserName];

or

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [LocalUserName];

On SQL Server to create Linked server to CSV file and query to use

For creating linked server to CSV file on a sql server use following syntax:

EXEC master.dbo.sp_addlinkedserver @server = N’TEST-CSV’, @srvproduct=N’CSVFLATFILE’, @provider=N’Microsoft.ACE.OLEDB.12.0′, @datasrc=N’\\FileServer\UNCPath\’, @provstr=N’Text;HDR=Yes’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’TEST-CSV’,@useself=N’False’,@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’collation compatible’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’data access’, @optvalue=N’true’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’dist’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’pub’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’rpc’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’rpc out’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’sub’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’connect timeout’, @optvalue=N’0′
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’collation name’, @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’lazy schema validation’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’query timeout’, @optvalue=N’0′
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’use remote collation’, @optvalue=N’true’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’remote proc transaction promotion’, @optvalue=N’true’
GO

And to query use 3 periods/dots as follows:

select * from [TEST-CSV]…[FileName#csv]

SQL Script to get list of linked server on SQL Server

Reference: This is awesome script to get list of linked server on SQL server.

SELECT ss.server_id 
          ,ss.name 
          ,'Server ' = Case ss.Server_id 
                            when 0 then 'Current Server' 
                            else 'Remote Server' 
                            end 
          ,ss.product 
          ,ss.provider 
          ,ss.catalog 
          ,'Local Login ' = case sl.uses_self_credential 
                            when 1 then 'Uses Self Credentials' 
                            else ssp.name 
                            end 
           ,'Remote Login Name' = sl.remote_name 
           ,'RPC Out Enabled'    = case ss.is_rpc_out_enabled 
                                   when 1 then 'True' 
                                   else 'False' 
                                   end 
           ,'Data Access Enabled' = case ss.is_data_access_enabled 
                                    when 1 then 'True' 
                                    else 'False' 
                                    end 
           ,ss.modify_date 
      FROM sys.Servers ss 
 LEFT JOIN sys.linked_logins sl 
        ON ss.server_id = sl.server_id 
 LEFT JOIN sys.server_principals ssp 
        ON ssp.principal_id = sl.local_principal_id