SQL Server Security best practices

• Minimize the number of SQL Server logins.


• Use Windows group logins to simplify ongoing management where possible.


• Disable logins rather than dropping them if there is any chance that they will be needed again.


• Ensure that expiry dates are applied to logins that are created for temporary purposes.


• Use fixed server-level roles to delegate server-level management responsibility, and only create user-defined server-level roles if your specific administrative delegation solution requires them.


• Disable the guest user in user databases unless you specifically require guest access.


• Aim to grant the minimum number of explicit permissions possible to meet the security requirements, and use membership of roles and inheritance to ensure the correct effective permissions.


• Ensure every user has only the permission they actually require.

Using SQL Server Restore to see Backup Media Set Info

restore headeronly
from disk = 'V:\backups\mydb.bak'
go

restore filelistonlyfrom disk = 'V:\backups\mydb.bak'
go

restore verifyonly
from disk = 'V:\backups\mydb.bak'
go

Bulk-Logged Recovery

These operations are minimally logged:

• SELECT INTO.
• Bulk load operations (bcp and BULK INSERT).
• CREATE INDEX (including indexed views).
• text and image operations (WRITETEXT and UPDATETEXT).

SQL Server DDL Change Control

-- =============================================
-- Author:      sqlforge.blogspot.com
-- Create date: 20141008
-- Description: audit ddl changes
--              compatible SQL Server 2005+
-- =============================================

USE [master]
GO


IF EXISTS (SELECT TOP 1 1 FROM [master].[sys].[server_triggers] WITH (NOLOCK) WHERE parent_class_desc = 'SERVER' AND name = N'tr_ObjectChangeAudit') -- sql server 2005+
   DISABLE TRIGGER [tr_ObjectChangeAudit] ON ALL SERVER
GO


IF EXISTS (SELECT TOP 1 1 FROM sysobjects WITH (NOLOCK) WHERE TYPE = 'U' AND name = 'changelog')
 BEGIN
    PRINT 'master.dbo.changelog exists, check to be sure no differences in table from older version'
 END
ELSE
 BEGIN
CREATE TABLE [dbo].[changelog](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [databasename] [varchar](256) NOT NULL,
 [eventtype] [varchar](50) NOT NULL,
 [objectname] [varchar](256) NOT NULL,
 [objecttype] [varchar](25) NOT NULL,
 [sqlcommand] [varchar](max) NOT NULL,
 [eventdate] [datetime] NOT NULL,
 [loginname] [varchar](256) NOT NULL
)

ALTER TABLE [dbo].[changelog] ADD  CONSTRAINT [df_changelog_eventdate]  DEFAULT (getdate()) FOR [eventdate]

 END
GO


IF  EXISTS (SELECT * FROM sys.objects WITH (NOLOCK) WHERE type = 'P' AND name = 'sp_ObjectChangeAudit' )
DROP PROCEDURE [dbo].[sp_ObjectChangeAudit]
GO

-- =============================================
-- Author:      sqlforge.blogspot.com
-- Create date: 20141008
-- Description: audit ddl changes
-- =============================================
CREATE PROCEDURE [dbo].[sp_ObjectChangeAudit]
( @databasename varchar(256)
, @eventtype varchar(50)
, @objectname varchar(256)
, @objecttype varchar(25)
, @sqlcommand varchar(max)
, @loginname varchar(256)
)
AS
BEGIN
   SET NOCOUNT ON;

     INSERT INTO [master].[dbo].[changelog]
               ( databasename
               , eventtype
               , objectname
               , objecttype
               , sqlcommand
               , loginname 
               )
          VALUES 
               ( @databasename
               , @eventtype
               , @objectname
               , @objecttype
               , @sqlcommand
               , @loginname 
               )
END --sp_ObjectChangeAudit
GO


IF EXISTS (SELECT TOP 1 1 FROM [master].[sys].[server_triggers] WITH (NOLOCK) WHERE parent_class_desc = 'SERVER' AND name = N'tr_ObjectChangeAudit') -- sql server 2005+
   DISABLE TRIGGER [tr_ObjectChangeAudit] ON ALL SERVER
GO

IF EXISTS (SELECT TOP 1 1 FROM [master].[sys].[server_triggers] WITH (NOLOCK) WHERE parent_class_desc = 'SERVER' AND name = N'tr_ObjectChangeAudit') -- sql server 2005+
   DROP TRIGGER [tr_ObjectChangeAudit] ON ALL SERVER
GO


-- =============================================
-- Author:      sqlforge.blogspot.com
-- Create date: 20141008
-- Description: audit ddl changes
-- =============================================
CREATE TRIGGER [tr_ObjectChangeAudit]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR create_procedure, alter_procedure, drop_procedure, create_table, alter_table, drop_table, create_function, alter_function, drop_function 
AS 
   DECLARE @databasename varchar(256)
         , @eventtype varchar(50)
         , @objectname varchar(256)
         , @objecttype varchar(25)
         , @sqlcommand varchar(max)
         , @loginname varchar(256)
BEGIN
   SET NOCOUNT ON; 
   SET ANSI_PADDING ON;

   DECLARE @data xml
   SET @data = eventdata() 

       SELECT @databasename = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
            , @eventtype = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)')
            , @objectname = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
            , @objecttype = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)')
            , @sqlcommand = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
            , @loginname = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)') 

BEGIN TRY
   EXEC [master].[dbo].[sp_ObjectChangeAudit]
        @databasename 
      , @eventtype 
      , @objectname 
      , @objecttype 
      , @sqlcommand
      , @loginname
END TRY
BEGIN CATCH
   PRINT 'SERVER TRIGGER tr_ObjectChangeAudit ' + ERROR_MESSAGE();
END CATCH
      
END --tr_ObjectChangeAudit


GO

DISABLE TRIGGER [tr_ObjectChangeAudit] ON ALL SERVER
GO

ENABLE TRIGGER [tr_ObjectChangeAudit] ON ALL SERVER
GO


/*****************************

IF EXISTS ( SELECT TOP 1 1 FROM sysobjects WHERE type = 'P' AND name = 'TESTME_01234' )
  DROP PROCEDURE TESTME_01234
GO

CREATE PROCEDURE TESTME_01234
AS
BEGIN
   SELECT 1;
END
GO

  DROP PROCEDURE TESTME_01234
GO


SELECT * FROM master.dbo.changelog ORDER BY eventdate DESC
GO
****************************************/

ODBC: Another 32/64bit MS undocumented "feature"

There are two different ODBC Administrator runtimes on a 64-bit machine. 

The first ODBC Manager is used to manage 64-bit data sources, while the second is used to manage 32-bit data sources. 

If you are running a 32-bit Operating System, you will have only 32 bit drivers installed. If you are using a 64 bit machine, the default ODBC Manager will be for 64-bit data sources. 

 If you have a 64bit OS and are trying to access a DSN in your 32bit application and receive the error check to see if you have a DSN configured for the architecture of your application ( 32-bit / 64-bit ).

“ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application” 

Use this ODBC Manager to Review 64-Bit Data Source Names 
c:\windows\system32\odbcad32.exe 

Use this ODBC Manager to Review 32-Bit Data Source Names c:\windows\sysWOW64\odbcad32.exe

Login History Server Trigger

use master
go

IF NOT EXISTS ( SELECT TOP 1 1 FROM master.sys.sysobjects WHERE type='U' AND name='ServerLoginHistory' )
 BEGIN
  CREATE TABLE [dbo].[ServerLoginHistory]
  (
   [SystemUser] [varchar](512) NULL,
   [HostName] [varchar](512) NULL,
   [DBUser] [varchar](512) NULL,
   [SPID] [int] NULL,
   [LoginTime] [datetime] NULL,
   [AppName] [varchar](512) NULL,
   [DatabaseName] [varchar](512) NULL
  ) 
 END
GO


IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'tr_ServerLoginHistory')
 BEGIN
   DROP TRIGGER [tr_ServerLoginHistory] ON ALL SERVER
 END
GO



CREATE TRIGGER [tr_ServerLoginHistory]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON 
AS
BEGIN
   IF ORIGINAL_LOGIN() NOT IN ( 'NT AUTHORITY\SYSTEM' )
    BEGIN
     INSERT INTO [ServerLoginHistory]
          SELECT ORIGINAL_LOGIN(), HOST_NAME(),USER, @@SPID, GETDATE(), APP_NAME(), DB_NAME()
    END
END --tr_ServerLoginHistory
GO


ENABLE TRIGGER [tr_ServerLoginHistory] ON ALL SERVER
GO
        
        
--DISABLE TRIGGER [tr_ServerLoginHistory] ON ALL SERVER
--GO

how to tell collation

SELECT 
   DATABASEPROPERTYEX('[DBNAME]', 'Collation') DBCollation
 , SERVERPROPERTY ('Collation') "ServerCollation";