Last logins on server

select max (login_time)as last_login_time, login_name from sys.dm_exec_sessions

group by login_name;


Last time run for Stored Proc

SELECT o.name,

       ps.last_execution_time

FROM   sys.dm_exec_procedure_stats ps

INNER JOIN

       sys.objects o

       ON ps.object_id = o.object_id

WHERE  DB_NAME(ps.database_id) = 'cabinet'

ORDER  BY

      [name] desc, ps.last_execution_time DESC 


https://stackoverflow.com/questions/595742/last-run-date-on-a-stored-procedure-in-sql-server

Create Windows Login

CREATE LOGIN [MyDomain\MyComputer$] FROM WINDOWS;

List Tables Quickly

SELECT * FROM SYSOBJECTS WHERE xtype = 'U'; GO

Table Sizes

SELECT

    t.NAME AS TableName,

    s.Name AS SchemaName,

    p.rows AS RowCounts,

    SUM(a.total_pages) * 8 AS TotalSpaceKB,

    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,

    SUM(a.used_pages) * 8 AS UsedSpaceKB,

    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,

    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,

    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB

FROM

    sys.tables t

INNER JOIN    

    sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

    sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN

    sys.schemas s ON t.schema_id = s.schema_id

WHERE

    t.NAME NOT LIKE 'dt%'

    AND t.is_ms_shipped = 0

    AND i.OBJECT_ID > 255

GROUP BY

    t.Name, s.Name, p.Rows

ORDER BY

    t.Name

   

--https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database

Link Server to Active Directory

USE [master]

GO

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'hadomain\administrator',@rmtpassword='@very$tr0ngP@$$w0rd1234'

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

https://blog.sqlauthority.com/2016/03/30/sql-server-query-active-directory-data-using-adsi-ldap-linked-server/

Query AD and Populate a Table with Data

USE [master]

GO

/****** Object:  StoredProcedure [dbo].[s_AD_Populate]    Script Date: 10/13/2019 3:02:52 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo].[s_AD_Populate]

AS

BEGIN

DECLARE @DomainFQDN VARCHAR(50) = 'snet.crouse.org';

IF OBJECT_ID('ADData') IS NOT NULL

  DROP TABLE ADData;

-- Query AD for all known user accounts

CREATE TABLE ADData(

    sAMAccountName               NVARCHAR(256),

    firstName           NVARCHAR(256),

    lastName            NVARCHAR(256),

    mail               NVARCHAR(256),

    EmployeeID          NVARCHAR(256),  --Our AD implementation uses the optional extensionAttributes, defining 1 as cost center

    manager              NVARCHAR(256),  --In @Query below, the name of this column is the same as the LDAP returned parameter, so no equate is applied in the query

    CompanyID             NVARCHAR(256),

       department      NVARCHAR(MAX),

       UserEnabled  INT,

    usnCreated          BIGINT,         --uSNCreated is an INT64 object type

       displayName NVARCHAR(MAX)

);

--Define the AD LDAP connection

IF NOT EXISTS(SELECT 1 FROM sys.servers WHERE name = 'ADSI')

 EXEC master.dbo.sp_addlinkedserver

    @server = N'ADSI',

    @srvproduct = N'Active Directory Services',

    @provider = N'ADsDSOObject',

    @datasrc = @DomainFQDN;

DECLARE @Rowcount int;

DECLARE @LastCreatedFilter VARCHAR(200) = '';

DECLARE @ADrecordsToReturn smallint = 901;  --AD will not return more than 901 records per query (changed from 1000 at some point). You can set it to any value smaller to control the 'pagesize' of returned results

--Loop mechanics:

-- - 1st loop: @Rowcount will be NULL but we need to looping to commence, thus ISNULL function

-- - Intermediate loops: Rowcount will equal the max number of requested records, indicating there may be more to query from AD

--SELECT @LastCreatedFilter = 'AND usnCreated = ''''<yourvalue>'''''; --Used during debugging to iniate the loop at a certain value

--DECLARE @TestStop int = 1;  -- @TestStop is a debug option to halt processing. It needs to be commented in or out at 3 places

WHILE ISNULL(@Rowcount,@ADrecordsToReturn) = @ADrecordsToReturn --AND @TestStop < 4  --Un-comment the three @TestStop lines to run a reduced sample query of AD, dictated by the value provided on this line (# of loops to process before stopping)

BEGIN

    DECLARE @Query VARCHAR (2000) =

     '

        SELECT TOP ' + CONVERT(varchar(10),@ADrecordsToReturn) + '

            sAMAccountName               = SamAccountName,

            firstName           = GivenName,

            lastName            = sn,

            mail               = mail,

            bsbcc               = EmployeeID,

            manager,

            CompanyID             = CompanyID,

                     department,

                     UserEnabled =UserAccountControl,

            usnCreated,

                     displayName

         FROM OpenQuery

          (

            ADSI,

            ''

                SELECT SamAccountName, GivenName, sn, mail, EmployeeID, manager, CompanyID, department, userAccountControl,usnCreated,displayName

                 FROM ''''LDAP://' + @DomainFQDN + '''''

                 WHERE objectCategory = ''''Person''''

                 AND objectClass = ''''user''''

                 ' + @LastCreatedFilter + '

                 ORDER BY usnCreated

            ''

          )

     '; 

    INSERT INTO ADData EXEC (@Query);

    SELECT @Rowcount = @@ROWCOUNT;

    SELECT @LastCreatedFilter = 'AND usnCreated > ' + LTRIM(STR((SELECT MAX(usnCreated) FROM ADData)));

--PRINT @LastCreatedFilter;  --While debugging, used to determine progress

--SET @TestStop = @TestStop + 1;  -- @TestStop is a debug option to halt processing. It needs to be commented in or out at 3 places

END;

--EXEC master.dbo.sp_dropserver 'ADSI';

END

--Do something with the results...

SELECT sAMAccountName, mail, EmployeeID, manager, CompanyID, department, UserEnabled, usnCreated FROM ADData

order by sAMAccountName;

--exec [dbo].[s_AD_Populate]

https://stackoverflow.com/questions/5661371/retrieve-901-rows-from-sql-server-2008-linked-server-to-active-directory


Convert EPOC time to DateTime

CREATE FUNCTION dbo.fn_ConvertToDateTime (@Datetime BIGINT) RETURNS DATETIME AS BEGIN DECLARE @LocalTimeOffset BIGINT ,@AdjustedLocalDatetime BIGINT; SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE()) SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset RETURN (SELECT DATEADD(second,@AdjustedLocalDatetime, CAST('1970-01-01 00:00:00' AS datetime))) END; GO










Convert time from DAY to DECIMAL

convert(decimal(10,2), datediff(minute, [dtAging], GETDATE()) / 60.0 /24)

Tracing

/*

SELECT * FROM sys.configurations WHERE configuration_id = 1568

SELECT * FROM ::fn_trace_getinfo(0)

*/

SELECT

     loginname,

     --loginsid,

     spid,

     hostname,

     --applicationname,

     --servername,

     databasename,

     --objectName,

     e.category_id,

     cat.name as [CategoryName],

     textdata,

     starttime,

     --eventclass,

     --eventsubclass,--0=begin,1=commit

     e.name as EventName

FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_55.trc',0)

     INNER JOIN sys.trace_events e

          ON eventclass = trace_event_id

     INNER JOIN sys.trace_categories AS cat

          ON e.category_id = cat.category_id

                --where loginname='CHNET\richlemmermann'

               -- where [textdata] like '%standby%'

              order by starttime desc


` http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/  


Dynamic Trace Log

CREATE PROC sp_GetTraceFileForLastMinute

 AS

  

/*

SELECT * FROM sys.configurations WHERE configuration_id = 1568

 

SELECT * FROM ::fn_trace_getinfo(0)

*/

 

DECLARE @TraceFile as NVARCHAR(MAX)

 

SELECT @TraceFile = CONVERT(NVARCHAR(MAX),[value]) FROM ::fn_trace_getinfo(0) WHERE property=2

 

--PRINT @TraceFile

  

SELECT

     loginname,

     --loginsid,

     spid,

     hostname,

     --applicationname,

     --servername,

     databasename,

     --objectName,

     e.category_id,

     cat.name as [CategoryName],

     textdata,

     starttime,

     --eventclass,

     --eventsubclass,--0=begin,1=commit

     e.name as EventName

FROM ::fn_trace_gettable(@TraceFile,0)

     INNER JOIN sys.trace_events e

          ON eventclass = trace_event_id

     INNER JOIN sys.trace_categories AS cat

          ON e.category_id = cat.category_id

                --where loginname='CHNET\richlemmermann'

              -- where [textdata] like '%standby%'

              --where e.name like '%user event%'

              where e.category_id not in (3)

              AND [starttime] > DATEADD(MINUTE,-1,GETDATE())

 

              order by starttime desc

 

 

dbo.procdroptable


ALTER PROCEDURE dbo.procdroptable

    @TABLENAME SYSNAME

AS

--https://dba.stackexchange.com/questions/39627/how-to-drop-tables-using-a-variable-in-sql-server

BEGIN

    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX)

    SELECT @SQL = 'DROP TABLE dbo.' + QUOTENAME(@TABLENAME) + '';

    EXEC sp_executesql @SQL;

END

GO


dbo.DBListing

CREATE TABLE [dbo].[SQLDBinfo](

     [sDBname] [nvarchar](255) NOT NULL,

     [sOwner] [nvarchar](255) NULL,

     [sDesc] [nvarchar](max) NULL,

     [dDateTime] [datetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

/*

--Add new DB's to table

INSERT INTO SQLDBinfo(sDBName)

select name from sys.databases

EXCEPT

select sDBName from SQLDBinfo

*/

--delete from SQLDBinfo where sDBName=''

--delete from SQLDBinfo

--exec DBListing

ALTER PROCEDURE DBListing

AS

BEGIN

INSERT INTO SQLDBinfo(sDBName)

select name from sys.databases

EXCEPT

select sDBName from SQLDBinfo

;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTSJob

                   ,'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)

,SSISJob AS

   (SELECT name, description, CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS Plans

    FROM   msdb.dbo.sysssispackages)

SELECT SSISJob.name AS Plan_Name

      ,SSISJob.Description AS Plan_Description

      ,Jobs.value  ('(SQLTask:SqlTaskData/@SQLTask:TaskName)[1]', 'NVARCHAR(MAX)') AS JOB_name

         ,DBname.value('(@SQLTask:DatabaseName)[1]','nvarchar(max)') AS DBname

         INTO #tmp_names

FROM  SSISJob

CROSS APPLY Plans.nodes('//DTSJob:ObjectData') Obj(Jobs)

CROSS APPLY Obj.Jobs.nodes('SQLTask:SqlTaskData/SQLTask:SelectedDatabases') DBS(DBname)

WHERE Jobs.exist('SQLTask:SqlTaskData') = 1

select DISTINCT sDBName,sOwner,sDesc,dDatetime,crdate,databasepropertyex(a.[name],'Recovery') as [Recovery],CASE WHEN is_parameterization_forced =0 THEN 'NO' ELSE 'YES' END AS 'parameterization_forced', Plan_Name

from

SQLDBinfo

JOIN master.dbo.sysdatabases a ON [name]=sDBName

JOIN sys.databases b ON a.[name]=b.[name]

LEFT OUTER JOIN #tmp_names c ON a.name=c.DBname

ORDER BY sDBName

--JOIN #tmp_names c ON a.name=c.DBname

END

--select *,databasepropertyex([name],'Recovery') as [Recovery] from master.dbo.sysdatabases

--select name,CASE WHEN is_parameterization_forced =0 THEN 'NO' ELSE 'YES' END AS 'parameterization_forced' from sys.databases order by Name

/*

CREATE TRIGGER SQLDBinfo_UpdateDateTime 

ON dbo.SQLDBinfo 

AFTER INSERT, UPDATE  

AS

UPDATE dbo.SQLDBinfo   SET dDateTime = GETDATE()

GO

drop TRIGGER SQLDBinfo_UpdateDateTime 

*/


Delete all data from all tables (even with triggers and constraints)


EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'

GO

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

GO

EXEC sp_MSForEachTable 'DELETE FROM ?'

GO

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

GO

EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'

GO


DDL_AUDIT_Logins (less info way)


USE [master]

GO

/****** Object:  DdlTrigger [DDL_AUDIT_Logins]    Script Date: 9/18/2019 1:07:27 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--https://solutioncenter.apexsql.com/auditing-security-changes-in-sql-server-2/

CREATE TRIGGER [DDL_AUDIT_Logins] ON ALL SERVER

FOR ADD_SERVER_ROLE_MEMBER

       ,DDL_GDR_SERVER_EVENTS

       ,DROP_SERVER_ROLE_MEMBER AS

SET NOCOUNT ON;

DECLARE @EventsTable TABLE (

       EType NVARCHAR(max)

       ,EObject VARCHAR(100)

       ,EDate DATETIME

       ,EUser VARCHAR(100)

       ,ECommand NVARCHAR(max)

       );

DECLARE @EType NVARCHAR(max);

DECLARE @ESchema NVARCHAR(max);

DECLARE @DBName VARCHAR(100);

DECLARE @Subject VARCHAR(200);

DECLARE @EObject VARCHAR(100);

DECLARE @EObjectType VARCHAR(100);

DECLARE @EMessage NVARCHAR(max);

DECLARE @ETSQL NVARCHAR(max);

--DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)

SELECT @EType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',

'nvarchar(max)')

,@ESchema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]',

'nvarchar(max)')

,@EObject = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',

'nvarchar(max)')

,@EObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]',

'nvarchar(max)')

,@DBName = EVENTDATA().value('

(/EVENT_INSTANCE/DatabaseName)[1]',

'nvarchar(max)')

,@ETSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'nvarchar(max)');

INSERT INTO @EventsTable

SELECT @EType

       ,@EObject

       ,GETDATE()

       ,SUSER_SNAME()

       ,@ETSQL;

SET @EMessage = 'Login_Event: ' + @EType + CHAR(10) + 'Event Occured at: '

+ Convert(VARCHAR, GETDATE()) + CHAR(10) + 'Changed Login: ' + @EObject +

CHAR(10) + 'Changed by: ' + SUSER_SNAME() + CHAR(10) + 'Executed T-SQL: ' +

@ETSQL

SELECT @Subject = 'SQL Server Login changed on ' + @@servername;

EXEC msdb.dbo.sp_send_dbmail @recipients = 'sysadmins@crouse.org'

       ,@body = @EMessage,

       @profile_name = 'mailserver'

              ,@subject = @Subject

       --,@body_format = 'HTML'

       ;

SET NOCOUNT OFF;

GO

ENABLE TRIGGER [DDL_AUDIT_Logins] ON ALL SERVER

GO

DDL_AUDIT_Logins (More precise information)

USE [master]
GO
/****** Object:  DdlTrigger [DDL_AUDIT_Logins]    Script Date: 11/4/2021 7:56:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--https://solutioncenter.apexsql.com/auditing-security-changes-in-sql-server-2/
--DROP TRIGGER [DDL_AUDIT_Logins] ON ALL SERVER
ALTER TRIGGER [DDL_AUDIT_Logins] ON ALL SERVER
--WITH EXECUTE AS 'chnet\xsqlserver'
FOR ADD_SERVER_ROLE_MEMBER
                ,DDL_GDR_SERVER_EVENTS
                ,DROP_SERVER_ROLE_MEMBER
                AS
SET NOCOUNT ON;
/*
IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL
  DROP TABLE dbo.Scores;
  */
  DECLARE @TraceFile as NVARCHAR(MAX)
SELECT @TraceFile = CONVERT(NVARCHAR(MAX),[value]) FROM ::fn_trace_getinfo(0) WHERE property=2
--PRINT @TraceFile
SELECT
     loginname,
     --loginsid,
     spid,
     hostname,
     --applicationname,
     --servername,
     databasename,
     --objectName,
     e.category_id,
     cat.name as [CategoryName],
     textdata,
     starttime,
     --eventclass,
     --eventsubclass,--0=begin,1=commit
     e.name as EventName
                INTO #tmp
FROM ::fn_trace_gettable(@TraceFile,0)
     INNER JOIN sys.trace_events e
          ON eventclass = trace_event_id
     INNER JOIN sys.trace_categories AS cat
          ON e.category_id = cat.category_id
                --where loginname='CHNET\richlemmermann'
              -- where [textdata] like '%standby%'
              --where e.name like '%user event%'
              where e.category_id not in (3)
              AND [starttime] > DATEADD(MINUTE,-10,GETDATE())
                                                  and left(cast(textdata as nvarchar(max)),6)='alter '
              order by starttime desc
DECLARE @query nvarchar(MAX)
--EXEC sp_GetTraceFileForLastMinute
declare @tab char(1) = CHAR(9)
DECLARE @Subject VARCHAR(200);
SELECT @Subject = 'SQL Server Login changed on ' + @@servername;
create table #TempAudit (Command varchar(50),SQLCommand varchar(5000),LoginName varchar(100),DBName varchar(500))
Insert into #TempAudit
SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') ,
   EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  ,
   EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)')  ,
   EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')
DECLARE @tableHTML NVARCHAR(max)
SET @subject = 'Critical Alert: Database State has been changed on : '
               + @@servername
      SET @tableHTML =
  '     <html><Body><style type="text/css">table {font-size:9.0pt;font-family:verdana;text-align:left;}tr {text-align:left;}
  h3 {display: block;font-size: 15.0pt;font-weight: bold; font-family: verdana;        text-align:left;       } </style><H3>Critical Alert: Database State has been changed on '+ @@servername + '</H3>' + N'<table border="1">'
  +N'<tr><th>Command</th><th>SQL Query</th><th>Command Executed By</th><th>Database Name</th></tr>'
                 + Cast((SELECT
         Command AS 'TD', '',
         SQLCommand AS 'TD', '',
         LoginName AS 'TD', '',
         DBName AS 'TD', ''
         FROM #TempAudit FOR xml path ( 'tr' ), root) AS NVARCHAR(max))
                 + N'</table>      </html>     </Body>'
--exec sp_TraceFile_DDL_AUDIT_Logins
--WAITFOR DELAY '00:00:5';
EXEC msdb.dbo.sp_send_dbmail @recipients = 'adminemail@domain.org'
                ,@profile_name = 'emailRelayServer'
                                ,@subject = @Subject
                ,@body_format = 'HTML'
   ,@body=@tableHTML
                --,@query='select * from #tmp'
                --,@attach_query_result_as_file = 1
                --, @query_attachment_filename='filename.csv',@query_result_separator=@tab,@query_result_no_padding=1
                ;
drop table #tmp
SET NOCOUNT OFF;
GO

TSQL Random String

SELECT LEFT(REPLACE(NEWID(),'-',''),10) AS Random10