mssql

• The Importance of Database Compatibility Level in SQL Server

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.

sql
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

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

List databases

sql
SELECT name, database_id, create_date FROM sys.databases; 

List tables

sql
select * from sysobjects where xtype='U'

Current audit level;

sql
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

sql
EXEC sp_readerrorlog 0, 1, 'Login failed' 

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

Going back further

Find All Failed SQL Server Logins

In profiler enable event UserConfigurable:0, then:

plaintext
EXEC sp_trace_generateevent 82, N'My message'

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

Use WITH RECOMPILE;

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