FirstResponderKit

 https://downloads.brentozar.com/FirstResponderKit.zip

Dynamics - where to find its cfg

 

SELECT *

  FROM [dbo].[SYSSERVERCONFIG]

SELECT *

  FROM [dbo].[SRSSERVERS]

SELECT *

  FROM [dbo].[BATCHSERVERCONFIG]

SQL Server: how to start the mail client and check its status

USE msdb ; 
EXEC msdb.dbo.sysmail_start_sp;
EXEC msdb.dbo.sysmail_help_status_sp;

Scripting Out the Logins, Server Role Assignments, and Server Permissions

 -- Scripting Out the Logins, Server Role Assignments, and Server Permissions

-- ************************************************************************************************************************

-- CRITICAL NOTE: You’ll need to change your results to display more characters in the query result.

-- Under Tools –> Options –> Query Results –> SQL Server –> Results to Text to increase the maximum number of characters 

-- returned to 8192 the maximum or to a number high enough to prevent the results being truncated.

-- ************************************************************************************************************************

SET NOCOUNT ON

-- Scripting Out the Logins To Be Created

SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+

   CASE 

WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' 

+ CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END

ELSE ' FROM WINDOWS WITH'

END 

   +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]

FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL

ON SP.principal_id = SL.principal_id

WHERE SP.type IN ('S','G','U')

AND SP.name NOT LIKE '##%##'

AND SP.name NOT LIKE 'NT AUTHORITY%'

AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name <> ('sa');

-- Scripting Out the Role Membership to Be Added

SELECT 

'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''

' AS [-- Server Roles the Logins Need to be Added --]

FROM master.sys.server_role_members SRM

JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id

JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id

WHERE SL.type IN ('S','G','U')

AND SL.name NOT LIKE '##%##'

AND SL.name NOT LIKE 'NT AUTHORITY%'

AND SL.name NOT LIKE 'NT SERVICE%'

AND SL.name <> ('sa');

-- Scripting out the Permissions to Be Granted

SELECT 

CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 

THEN SrvPerm.state_desc 

ELSE 'GRANT' 

END

    + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' + 

CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 

THEN '' 

ELSE ' WITH GRANT OPTION' 

END collate database_default AS [-- Server Level Permissions to Be Granted --] 

FROM sys.server_permissions AS SrvPerm 

JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 

WHERE   SP.type IN ( 'S', 'U', 'G' ) 

AND SP.name NOT LIKE '##%##'

AND SP.name NOT LIKE 'NT AUTHORITY%'

AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name <> ('sa');

SET NOCOUNT OFF

SQL Server: Connection_Check

 USE [dbatoolbox]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Connections_Full](

[server] [nvarchar](130) NOT NULL,

[name] [nvarchar](130) NOT NULL,

[login_time] [datetime] NOT NULL,

[last_batch] [datetime] NOT NULL,

[timestamp] [datetime] NOT NULL,

[status] [nvarchar](30) NOT NULL,

[hostname] [nvarchar](130) NOT NULL,

[program_name] [nvarchar](130) NOT NULL,

[nt_username] [nvarchar](130) NOT NULL,

[loginame] [nvarchar](130) NOT NULL

) ON [PRIMARY]

GO

ALTER PROCEDURE [dbo].[usp_ConnectionsCount_full] 

AS

BEGIN

 SET NOCOUNT ON;

INSERT INTO Connections_Full 

  SELECT @@ServerName AS SERVER

 ,NAME

 ,login_time

 ,last_batch

 ,getdate() AS DATE

 ,status

 ,hostname

 ,program_name

 ,nt_username

 ,loginame

FROM sys.databases d

LEFT JOIN sys.sysprocesses sp ON d.database_id = sp.dbid

WHERE database_id NOT BETWEEN 0

  AND 4

 AND loginame IS NOT NULL

END

db_mail_cleanup

 -- https://blog.devart.com/reduce-msdb-big-size.html

DECLARE @DateBefore DATETIME 

SET @DateBefore = DATEADD(DAY, -30, GETDATE())

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DateBefore --, @sent_status = 'sent'

EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @DateBefore

SQLPlus Backspace in Linux

To able to use only the backspace button to erase characters in sqlplus,

you basically need to add:

stty erase ^H

in your .bash_profile