select max (login_time)as last_login_time, login_name from sys.dm_exec_sessions
group by login_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 LOGIN [MyDomain\MyComputer$] FROM WINDOWS;
SELECT
*
FROM
SYSOBJECTS
WHERE
xtype = 'U';
GO
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
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/
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(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/
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
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
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
*/
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
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