mssql

Use WITH RECOMPILE;

CREATE PROCEDURE myProc
	@myParam int ...
	WITH RECOMPILE
AS
SELECT ...

To preserve time zone information, use CONVERT(datetime, @yourDateTimeOffset, 1). This particular usage of 1 in what is otherwise called the style parameter doesn’t seem to be documented for datetimeoffset > datetime, but does work.

declare @createdon datetimeoffset
set @createdon = '2008-12-19 10:00:00 +10:00'

select CONVERT(datetime, @createdon)
--Output: 2008-12-19 10:00:00.000

select CONVERT(datetime, @createdon, 1)
--Output: 2008-12-19 00:00:00.000

Trailing spaces are a bit whacky in mssql, check this.

In profiler enable event UserConfigurable:0, then:

EXEC sp_trace_generateevent 82, N'My message'

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE MyDatabase
GO

• The Importance of Database Compatibility Level in SQL Server

Current audit level;

execute xp_loginconfig 'audit level';

Audit level. Possible values are none, success, failure, and all. Audits are written to the error log and to the Windows Event Viewer.

xp_loginconfig docs

xp_loginconfig cannot be used to set configuration values. To set the login mode and audit level, use SQL Server Management Studio.

SSMS

Database instance > Right-click > Properties > Security

Seeing failed logins

EXEC sp_readerrorlog 0, 1, 'Login failed' 

where 0 is the log number - increment to keep going back in time.

Going back further

https://www.mssqltips.com/sqlservertip/4941/find-all-failed-sql-server-logins/

List databases

SELECT name, database_id, create_date FROM sys.databases; 

List tables

select * from sysobjects where xtype='U'