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.

Work Adeventure: Backup database to network

I had a situation where I had to perform a backup of the database on a network. I performed the following

Mapped the  nwMachine\L share to Y drive.
Thus I had (\\nwMachine\L) Y:

If I do a backup at this point the SQL server will not see the mapped drive (Y:) yet .
I had to run few scripts to make SQL server see the mapped Y drive.

EXEC sp_configure 'advanced', 1

RECONFIGURE WITH override

GO

--to enable the xp_cmdshell
--once the job is done make sure this is turned off

EXEC sp_configure 'xp_cmdshell',1

RECONFIGURE WITH override

GO


now make sql server see the mapped drive
EXEC xp_cmdshell 'net use Y: \\nwMachine\L
GO



--EXEC xp_cmdshell 'net use <local mapped drive> (space)  <shared path>
 
--Once we get result as "command ran successfully" we are successful.

--Now in the backup wizard the Y drive should appear along with the other local drives.

--now turn off the xp_cmdshell

EXEC sp_configure 'xp_cmdshell',0

RECONFIGURE WITH override

GO

Work adeventure : Permission to view sql error log (ONLY)

I had a situation where I had to give a user permission to view the sqlerror log only. I achieved by doing the following

• Create a login  DBA_error_logUser  in the server
• Added the login to the securityadmin server role ( to view sql error log the login must be the member of this server role)
• Created a user DBA_error_logUser  with map to the login to grant access to the error logs
• Deny Alter to any Login 
• Grant permission to view Sql Server Logs for the user
• Create a log on Trigger to deny access to Query Window

---Create a login  DBA_error_logUser  in the server

use [master]
GO

CREATE LOGIN [DBA_ErrorLogUser] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO


--Added the login to the securityadmin server role
EXEC master..sp_addsrvrolemember @loginame = N'DBA_ErrorLogUser', @rolename = N'securityadmin'
 GO


--Created a user DBA_error_logUser  with map to the login to grant access to the error logs

CREATE USER [DBA_ErrorLogUser] FOR LOGIN [DBA_ErrorLogUser] GO

--Deny Alter to any Login

DENY ALTER ANY LOGIN TO DBA_ErrorLogUser GO


--Grant permission to view Sql Server Logs for the user

Grant EXECUTE ON master.sys.xp_readerrorlog TO DBA_ErrorLogUser GO

--Create a log on Trigger to deny access to Query Window

  IF EXISTS ( SELECT * FROM master.sys.server_triggers
                WHERE parent_class_desc = 'SERVER' AND name = N'Deny_QueryWindowLogin_Trigger' )

DROP TRIGGER [Deny_QueryWindowLogin_Trigger] ON ALL SERVER
 GO

Create TRIGGER Deny_QueryWindowLogin_Trigger ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON AS
BEGIN
   DECLARE @data XML
   SET @data = EVENTDATA()
  DECLARE @AppName SYSNAME, @LoginName SYSNAME

  SELECT @AppName = [program_name] FROM sys.dm_exec_sessions WHERE session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

SELECT @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname') IF @AppName= 'Microsoft SQL Server Management Studio - Query' AND @LoginName = 'DBA_ErrorLogUser'

BEGIN
             ROLLBACK ; --Disconnect the session
END
 END ;

Work adventure: I/O error 21(The device is not ready )

We had an error all of a sudden on our sql server

"Microsoft OLE DB Provider for SQL Server:
I/O error 21(The device is not ready.) detected during read at offset 0x0000a7a239c000 in file 'DataFileName'.
- HY000 - HRESULT=0x80004005 “

Since the error message mentions that the drive on which one of the data file resided was not ready, it was obvious that the underlying storage was the problem.
But the System Event Log did not have any error messages related to storage. The database was online and accessible. But when I  tried accessing the Properties of this database, the same error was thrown.


The reason for this error was not something that happened at that time. Another email chain from the Windows Administrators sent a few hours ago said it all. They had found that the drive letter associated with the Mount Point hosting this data file was missing and they had *mapped* it somehow. Not sure how they had done the drive mapping. Most likely the drive hosting this data file got disconnected while the database was online.


I fixed this by bringing the database offline and back online after few seconds

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE <dbname> SET ONLINE