Add linked servers

--view linked servers



exec sp_linkedservers




--OR



select * from sys.servers where is_linked = 1



　

--add a linked server


EXEC sp_addlinkedserver

@server=N'STAGINGCONNECT',

@srvproduct=N'SQL Sever',

@provider=N'SQLNCLI',

@datasrc=N'DB7830F';



　

　

--view the linked servers and the logins to the linked server



select s.name, p.principal_id, l.remote_name

from sys.servers s

join sys.linked_logins l

on s.server_id = l.server_id

left join sys.server_principals p

on l.local_principal_id = p.principal_id

where s.is_linked = 1

Work Adventure: Fix Orphaned user

When I restored a database to a new server, the users were imported but the login were not. The database users were  imported as the orphan users in SQL 2012

Here is how I found the orphaned users in a database

EXEC sp_change_users_login @Action='Report'

--OR we can run the following in the current database context

select * from sysusers where issqluser = 1 and (sid <> 0x0

and sid is not null)

and (LEN(sid) <= 16 ) AND suser_sname(sid) is null 





-----------------------------------------

The in order to link the orphan user to the existing login  I did the following


sp_change_users_login 'Update_One', 'OrphanUser, 'Logintolinkto'

 

Configuring Dbmail

--check for operators
use msdb
Go
select * from msdb.dbo.sysoperators

--add operators
use msdb
Go
exec dbo.sp_add_operator @name= User1, @enabled = 1, @email_address = 'user1e@abc.com'
exec dbo.sp_add_operator @name= 'User2, @enabled = 1, @email_address = 'user2@abc.com'

--enable the database mail
USE master
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO

--Set up account and profile.


--One last thing but most important thing left is Letting the SQL Server Agent Talk to Database Mail. If we do not perform this step we get the following error
Avoiding 'Failed to notify <operator> via email' errors

1. -Highlight the "SQL Server Agent" in the object Explorer
2. -Go to Properties
3. -Go to Alert system options
4. - Check the enable profile on and select the profile

 

Error: Failed to notify via email

The above error occurs when we forget to perform the step to let the SQL Server Agent Talk to Database Mail

To perform that , follow the following steps -

-Highlight the "SQL Server Agent" in the object Explorer
-Go to Properties
-Go to Alert System options
- Check the enable profile on and select the profile  in the mail Session section.