Automate Monitoring SQL Server Error Logs with Email Alerts

Automate Monitoring SQL Server Error Logs with Email Alerts

Get Alerts for Specific SQL Server Login Failed Events

Get Alerts for Specific SQL Server Login Failed Events

Script to configure SQL database Mail

USE master;
GO
exec sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO
exec sp_configure 'Database Mail XPs','1'
RECONFIGURE WITH OVERRIDE;
GO

use msdb
go
exec sysmail_start_sp

/*
exec msdb.dbo.sysmail_delete_profile_sp @profile_name = 'default' ;
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'default' ;
*/  
 
DECLARE @profile_name sysname,
        @account_name sysname,
        @SMTP_servername sysname,
        @email_address NVARCHAR(128),
   @display_name NVARCHAR(128);

-- Profile name. Replace with the name for your profile
        SET @profile_name = 'default';

-- Account information. Replace with the information for your account.

SET @account_name = 'default';
SET @SMTP_servername = '10.11.150.25';
SET @email_address = REPLACE(@@servername,'-','')+'@csod.com';
        SET @display_name = REPLACE(@@servername,'-','');


-- Verify the specified account and profile do not already exist.
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
  RAISERROR('The specified Database Mail profile () already exists.', 16, 1);
  GOTO done;
END;

IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
 RAISERROR('The specified Database Mail account () already exists.', 16, 1) ;
 GOTO done;
END;

-- Start a transaction before adding the account and the profile
BEGIN TRANSACTION ;

DECLARE @rv INT;

-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
    @account_name = @account_name,
    @email_address = @email_address,
    @display_name = @display_name,
    @mailserver_name = @SMTP_servername;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail account ().', 16, 1) ;
    GOTO done;
END

-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
    @profile_name = @profile_name ;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail profile ().', 16, 1);
ROLLBACK TRANSACTION;
    GOTO done;
END;

-- Associate the account with the profile.
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = @profile_name,
    @account_name = @account_name,
    @sequence_number = 1 ;

IF @rv<>0
BEGIN
    RAISERROR('Failed to associate the speficied profile with the specified account ().', 16, 1) ;
ROLLBACK TRANSACTION;
    GOTO done;
END;

COMMIT TRANSACTION;

done:

GO

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @principal_name = 'public',
    @profile_name = 'default',
    @is_default = 1 ;
GO


EXEC msdb.dbo.sp_send_dbmail @recipients = 'palexander@csod.com', @subject='test', @body='test', @body_format = 'TEXT'

/*

http://technet.microsoft.com/en-us/library/ms177580(v=sql.90).aspx

*/

sp_job_steps_history : Job History for Steps

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[sp_job_steps_history]    Script Date: 02/08/2012 08:13:52 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_job_steps_history]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_job_steps_history]
GO


/*
--all failed steps
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = null, @runstatus = 0 , @stepid = null

--all failed specific steps
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = null, @runstatus = 0 , @stepid = 15

--outcome of the specific step for all jobs all dates
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = null, @runstatus = null , @stepid = 15

--outcome of the specific step for all jobs specific date
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = 20120208, @runstatus = null , @stepid = 15

--all failed steps for given day
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = 20120208, @runstatus = 0 , @stepid = null

--all failed steps for given day specific step
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = 20120208, @runstatus = 0 , @stepid = 13

--all failed steps for given job specific date
exec msdb.dbo.sp_job_steps_history @jobname = '%AMERICANSUPPORT%' , @rundate = 20120208, @runstatus = 0 , @stepid = null

--all failed steps for given job all dates
exec msdb.dbo.sp_job_steps_history @jobname = '%AMERICANSUPPORT%' , @rundate = null, @runstatus = 0 , @stepid = null

--specific job status for given dae
exec msdb.dbo.sp_job_steps_history @jobname = '%AMERICANSUPPORT%' , @rundate = 20120208, @runstatus = null , @stepid = null

*/
CREATE PROCEDURE dbo.sp_job_steps_history
(
@jobname nvarchar(256) = null
,@rundate int = null
,@runstatus int = 0
,@stepid int = null
)
as
BEGIN
/*
@runstatus:

0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
*/

/*
set @jobname = null
set @rundate = null
set @runstatus = 3
*/

SELECT j.name JobName,h.step_id, h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as ExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
where (j.name like @jobname or @jobname is null)
and (h.run_date = @rundate or @rundate is null)
and (h.run_status = @runstatus or @runstatus is null)
and (h.step_id = @stepid or @stepid is null)
ORDER BY j.name, h.run_date, h.run_time

END
GO

What's the Point of Using VARCHAR(n) Anymore?

What's the Point of Using VARCHAR(n) Anymore?

SQL Orphan users

select u.name , 'exec sp_revokedbaccess '''+u.name+''''
from master..syslogins l right join
    sysusers u on l.sid = u.sid
    where l.sid is null and issqlrole <> 1 and isapprole <> 1  
    and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
    and u.name <> 'system_function_schema')
   

Win 2008 Run As

Create a batch file that has the following on SQL server:

cd\
cd C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE
runas /user:csodmgmt\palexander ssms

Get Alerts for Specific SQL Server Login Failed Events

Get Alerts for Specific SQL Server Login Failed Events

Active Directory from SQL

Configuration on Server

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'[10.10.10.10]'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'domain\user',@rmtpassword='password'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible',  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation',  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation',  @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO



select * from openquery
(
ADSI,'SELECT NAME,SAMAccountName
FROM ''LDAP://10.10.10.10''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
') AS tblADSI
ORDER BY SAMAccountName


select * from openquery
(
ADSI,'SELECT NAME,SAMAccountName,displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber
FROM ''LDAP://10.10.10.10''
WHERE objectCategory = ''Person''
AND objectClass = ''user''
AND sAMAccountName = ''username''
') AS tblADSI
ORDER BY displayname




Find groups and users all together



-- Configure AD Domain to search

DECLARE @strDC NVARCHAR(100)
SET @strDC = 'dc=csodmgmt,dc=corp'

-- Declare Variables

DECLARE @chvAlphaChars VARCHAR(60),
@chvSearch VARCHAR(10),
@chvSearchLevel1 VARCHAR(1),
@chvSearchLevel2 VARCHAR(1),
@chvSearchLevel3 VARCHAR(1),
@intcountLevel1 INT,
@intcountLevel2 INT,
@intcountLevel3 INT,
@intRowCount INT,
@strSQL NVARCHAR(4000),
@strADSISQL NVARCHAR(4000),
@Login VARBINARY(85),
@CN VARCHAR(512),
@CT INT,
@ADRoot VARCHAR(255)


-- Declare temp tables

IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_Users'))
DROP TABLE #LDAP_AD_V2_USERS

CREATE TABLE [DBO].[#LDAP_AD_V2_USERS] (
[ROW_ID] [INT] IDENTITY ( 1,1 ) NOT NULL,
[SID] [VARBINARY](85) NULL,
[SAMACCOUNTNAME] [NVARCHAR](256) NULL,
[CN] [NVARCHAR](256) NULL,
[SN] [NVARCHAR](256) NULL,
[DISPLAYNAME] [NVARCHAR](256) NULL,
[GIVENNAME] [NVARCHAR](256) NULL,
[TELEPHONENUMBER] [NVARCHAR](256) NULL,
[ADSPATH] [NVARCHAR](256) NULL,
[HOMEDIRECTORY] [NVARCHAR](256) NULL,
[MAIL] [NVARCHAR](256) NULL,
[MEMBEROF] [NVARCHAR](256) NULL,
[PRIMARYGROUPID] [INT],
[CREATETIMESTAMP] DATETIME)
ON [PRIMARY]

IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_Groups'))
DROP TABLE #LDAP_AD_V2_GROUPS

CREATE TABLE [DBO].[#LDAP_AD_V2_GROUPS] (
[ID] [INT] IDENTITY ( 1,1 ) NOT NULL,
[LOGIN] [VARCHAR](512) NULL,
[EMAIL] [VARCHAR](255) NULL,
[ACCTNAME] [VARCHAR](512) NULL,
[DISTNAME] [VARCHAR](512) NULL,
[CREATEDDATE] [DATETIME] NULL,
[CHANGEDDATE] [DATETIME] NULL,
[MGR] [VARCHAR](512) NULL,
[SID] [VARBINARY](85) NULL)
ON [PRIMARY]

IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_UserGroups'))
DROP TABLE #LDAP_AD_V2_USERGROUPS

CREATE TABLE [DBO].[#LDAP_AD_V2_USERGROUPS] (
[USERLOGIN] [VARBINARY](85) NOT NULL,
[GROUPLOGIN] [VARBINARY](85) NULL)
ON [PRIMARY]

-- POPULATION OF USERS

-- Search letters to cycle through
-- any chars, but the first char must be a space
SET @chvAlphaChars = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ.-_`0123456789'
SET NOCOUNT ON

-- start on non space char
SET @intcountLevel1 = 2
-- first level loop
WHILE @intcountLevel1 <= LEN(@chvAlphaChars)
BEGIN
-- get first level char
SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)
-- reset start on space
SET @intcountLevel2 = 1
-- second level loop
WHILE @intcountLevel2 <= LEN(@chvAlphaChars)
BEGIN
-- reset start on space
SET @intcountLevel3 = 1
-- third level loop
WHILE @intcountLevel3 <= LEN(@chvAlphaChars)
BEGIN
-- setup the string to search for. By using the trim function we can form each level depending on no records
-- eg A 99, B 1000 > BA 9, BB 20 etc
-- trim the spaces forming just A, B, C ; AA, AB for search etc
SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)
SET @chvSearchLevel2 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel2,1))
SET @chvSearchLevel3 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel3,1))
SET @chvSearch = @chvSearchLevel1 + @chvSearchLevel2 + @chvSearchLevel3
SET @strADSISQL = 'select objectSid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath, homedirectory, mail, primarygroupid, createTimeStamp '
+ CHAR(13) + 'from ''''LDAP://' + @strDC + ''''' '
+ CHAR(13) + 'where objectCategory = ''''Person'''' '
+ CHAR(13) + 'and objectClass = ''''user'''' '
+ CHAR(13) + 'and sAMAccountName = ''''' + @chvSearch + '*'''' '
SET @strSQL = 'insert into #LDAP_AD_V2_Users (sid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath, homedirectory, mail, primarygroupid, createTimeStamp) '
+ CHAR(13) + 'select objectSid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath, homedirectory, mail, primarygroupid, createTimeStamp '
+ CHAR(13) + 'from openquery(ADSI,''' + @strADSISQL + ''' ) '
+ CHAR(13) + 'order by sAMAccountName'

EXEC SP_EXECUTESQL @strSQL

SET @intRowCount = @@ROWCOUNT

-- prints what string is being searched for : no of inserts
-- PRINT @chvSearch + ' : ' + CONVERT(VARCHAR,@intRowCount)

-- if searched on @chvSearchLevel1 and under 1000 then everything is fine so skip search2 to next search1 eg A > B
IF @intRowCount < 1000
AND @chvSearchLevel2 = ''
SET @intcountLevel2 = @intcountLevel2 + 100

-- if searched on @chvSearchLevel2 and under 1000 then everything is fine so skip to next search2 eg AA > AB
IF @intRowCount < 1000
AND @chvSearchLevel3 = ''
SET @intcountLevel3 = @intcountLevel3 + 100

-- else over 1000 so increment third level
SET @intcountLevel3 = @intcountLevel3 + 1
END

-- increment next second level char
SET @intcountLevel2 = @intcountLevel2 + 1
END

-- increment next first level char
SET @intcountLevel1 = @intcountLevel1 + 1
END


-- POPULATION OF GROUPS

SET @strADSISQL = 'select objectSid, managedBy, whenChanged, whenCreated, distinguishedName, name, samAccountName, mail '
+ CHAR(13) + 'FROM ''''LDAP://' + @strDC + ''''' '
+ CHAR(13) + 'WHERE objectCategory = ''''Group'''' '

SET @strSQL = 'insert into #LDAP_AD_V2_Groups (sid,mgr,changeddate,createddate,distname,acctname,email,login) '
+ CHAR(13) + 'select objectSid,managedBy,whenChanged,whenCreated,distinguishedName,name,mail,samAccountName '
+ CHAR(13) + 'from openquery(ADSI,''' + @strADSISQL + ''' ) '
+ CHAR(13) + 'order by sAMAccountName'

-- PRINT @strSQL
EXEC SP_EXECUTESQL @strSQL


-- POPULATION OF USER > GROUPS RELATIONSHIP TABLE

IF EXISTS (SELECT *
FROM TEMPDB.DBO.SYSOBJECTS
WHERE ID = OBJECT_ID('tempdb.dbo.#CT'))
DROP TABLE #CT

-- Create a temporary table to hold AD user SID values
CREATE TABLE #CT (CT VARBINARY(85))

-- Declare and open a cursor to step through the list of Active Directory groups
DECLARE CURGROUPS CURSOR FOR
SELECT SID,
DISTNAME
FROM #LDAP_AD_V2_GROUPS
ORDER BY LOGIN

OPEN CURGROUPS

FETCH NEXT FROM CURGROUPS
INTO @Login,
@CN

WHILE @@FETCH_STATUS = 0
BEGIN
-- Empty the temp table
TRUNCATE TABLE #CT

-- Build a SQL statement to insert the SID values directly from the linked server into the temp table
SET @strsql = 'INSERT #CT
SELECT *
FROM OPENQUERY (
ADSI,
''SELECT objectSid
FROM ''''LDAP://' + @strDC + '''''
WHERE objectCategory = ''''User''''
AND memberof=''''' + REPLACE(@CN,'''','''''''''') + ''''''')'

EXEC( @strsql)

-- Select the number of records inserted. If this value is less than 1000 then there is no need
-- to execute the OLE calls, and we simply copy the values into the correlation table.
SELECT @CT = COUNT(* )
FROM #CT

--PRINT @ct
--PRINT @strsql

IF @CT <> 0
BEGIN
INSERT #LDAP_AD_V2_USERGROUPS
(USERLOGIN,
GROUPLOGIN)
SELECT CT,
@Login
FROM #CT
WHERE CT IS NOT NULL
END

FETCH NEXT FROM CURGROUPS
INTO @Login,
@CN
END

CURSORERROR:
CLOSE CURGROUPS
DEALLOCATE CURGROUPS
DROP TABLE #CT

/*
-- Individual SQL Statements to see users, groups & relationships
select * from #LDAP_AD_V2_Users
select * from #LDAP_AD_V2_Groups
select * from #LDAP_AD_V2_UserGroups
*/

-- Final Query to tie together the 3 tables and produce a report of group membership

SELECT #LDAP_AD_V2_GROUPS.ACCTNAME AS GROUPNAME,
#LDAP_AD_V2_USERS.SAMACCOUNTNAME AS USERACCOUNT
FROM #LDAP_AD_V2_USERGROUPS
INNER JOIN #LDAP_AD_V2_USERS
ON #LDAP_AD_V2_USERGROUPS.USERLOGIN = #LDAP_AD_V2_USERS.SID
INNER JOIN #LDAP_AD_V2_GROUPS
ON #LDAP_AD_V2_USERGROUPS.GROUPLOGIN = #LDAP_AD_V2_GROUPS.SID
where #LDAP_AD_V2_USERS.SAMACCOUNTNAME = ' username '
ORDER BY #LDAP_AD_V2_GROUPS.ACCTNAME,
#LDAP_AD_V2_USERS.SAMACCOUNTNAME

Format drives with correct allocation and offset for maximum SQL Server performance

Format drives with correct allocation and offset for maximum SQL Server performance