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

List databases

        
sql
        

            
        
     
SELECT name, database_id, create_date FROM sys.databases; 

List tables

        
csharp
        

            
        
     
select * from sysobjects where xtype='U'

        
sql
        

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

Current audit level;

        
lua
        

            
        
     
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:

        
sql
        

            
        
     
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 ...