Security Update for SQL Server 2014 Service Pack 2 GDR (KB3194714)

    “KB Articles: KB3194714 Security bulletins:SQL Server 2014 MS16-136 Security issues have been identified in SQL Server 2014 Service Pack 2 that could allow an attacker to compromise your system and gain control over it. You can help protect your computer by installing this update from Microsoft. After you install this item, you may have to restart your computer.”    ….  source Microsoft website
      Click here for the download/installation link.


Microsoft R – SQL 2016 – Notes

“R is a free software environment for statistical computing and graphics” – source click here.

Useful links:


Quick notes:

  • You need Windows Server 2012 R2
  • JDK needs to be installed before running the SQL 2016 setup program
  • Enable TCP/IP and restart your SQL 2016 instance.
  • Connect to the instance.
  • Run:Exec sp_configure ‘external scripts enabled’, 1;reconfigure;
  • Restart all services, SDE, SSA, Polybase, Launchpad service.
  • Note. If you don’t restart the Launchpad service, you will get below error message.
  • Msg 39011, Level 16, State 1, Line 8
    SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service.
    Msg 11536, Level 16, State 1, Line 8
    EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

r1 f2

SQL Server Fixed Server Role Permissions

SQL Server Fixed Server Role Permissions – run – sp_srvrolepermission

ServerRole Permission

bulkadmin Add member to bulkadmin

bulkadmin BULK INSERT

dbcreator Add member to dbcreator




dbcreator Extend database


dbcreator RESTORE LOG

dbcreator sp_renamedb

diskadmin Add member to diskadmin

diskadmin DISK INIT

diskadmin sp_addumpdevice

diskadmin sp_diskdefault

diskadmin sp_dropdevice

processadmin Add member to processadmin

processadmin KILL

securityadmin Add member to securityadmin

securityadmin Grant/deny/revoke CREATE DATABASE

securityadmin Read the error log

securityadmin sp_addlinkedsrvlogin

securityadmin sp_addlogin

securityadmin sp_defaultdb

securityadmin sp_defaultlanguage

securityadmin sp_denylogin

securityadmin sp_droplinkedsrvlogin

securityadmin sp_droplogin

securityadmin sp_dropremotelogin

securityadmin sp_grantlogin

securityadmin sp_helplogins

securityadmin sp_password

securityadmin sp_remoteoption (update)

securityadmin sp_revokelogin

serveradmin Add member to serveradmin

serveradmin dbcc freeproccache

serveradmin RECONFIGURE

serveradmin SHUTDOWN

serveradmin sp_configure

serveradmin sp_fulltext_service

serveradmin sp_tableoption

setupadmin Add member to setupadmin

setupadmin Add/drop/configure linked servers

setupadmin Mark a stored procedure as startup

sysadmin Add extended procedures

sysadmin Add member to bulkadmin

sysadmin Add member to dbcreator

sysadmin Add member to diskadmin

sysadmin Add member to processadmin

sysadmin Add member to securityadmin

sysadmin Add member to serveradmin

sysadmin Add member to setupadmin

sysadmin Add member to sysadmin

sysadmin Add/drop to/from db_accessadmin

sysadmin Add/drop to/from db_backupoperator

sysadmin Add/drop to/from db_datareader

sysadmin Add/drop to/from db_datawriter

sysadmin Add/drop to/from db_ddladmin

sysadmin Add/drop to/from db_denydatareader

sysadmin Add/drop to/from db_denydatawriter

sysadmin Add/drop to/from db_owner

sysadmin Add/drop to/from db_securityadmin

sysadmin Add/drop/configure linked servers

sysadmin All DDL but GRANT, REVOKE, DENY



sysadmin BACKUP LOG

sysadmin BULK INSERT


sysadmin Complete SETUSER SQL user

sysadmin Constraints on System tables


sysadmin Create indices on system tables

sysadmin Create/delete/modify system tables

sysadmin dbcc change ‘on’ rules

sysadmin dbcc checkalloc

sysadmin dbcc checkdb

sysadmin dbcc checkfilegroup

sysadmin dbcc checkident

sysadmin dbcc checktable

sysadmin dbcc cleantable

sysadmin dbcc dbreindex

sysadmin dbcc dropcleanbuffers

sysadmin dbcc freeproccache

sysadmin dbcc inputbuffer

sysadmin dbcc outputbuffer

sysadmin dbcc pintable

sysadmin dbcc proccache

sysadmin dbcc setcpuweight

sysadmin dbcc setioweight

sysadmin dbcc show_statistics

sysadmin dbcc showcontig

sysadmin dbcc showoptweights

sysadmin DBCC ShrinkDatabase

sysadmin dbcc shrinkfile

sysadmin dbcc traceon/off

sysadmin dbcc updateusage

sysadmin DELETE permission on any object

sysadmin DENY

sysadmin DISK INIT


sysadmin EXECUTE any procedure

sysadmin Extend database

sysadmin GRANT

sysadmin Grant/deny/revoke CREATE DATABASE

sysadmin INSERT permission on any object

sysadmin KILL

sysadmin Mark a stored procedure as startup

sysadmin Raiserror With Log

sysadmin Read the error log


sysadmin REFERENCES permission on any table


sysadmin RESTORE LOG

sysadmin REVOKE

sysadmin SELECT permission on any object

sysadmin SHUTDOWN

sysadmin sp_addapprole

sysadmin sp_addlinkedsrvlogin

sysadmin sp_addlogin

sysadmin sp_addrole

sysadmin sp_addrolemember

sysadmin sp_addumpdevice

sysadmin sp_adduser

sysadmin sp_altermessage

sysadmin sp_approlepassword

sysadmin sp_change_users_login

sysadmin sp_changedbowner

sysadmin sp_changeobjectowner

sysadmin sp_configure

sysadmin sp_dbcmptlevel

sysadmin sp_defaultdb

sysadmin sp_defaultlanguage

sysadmin sp_denylogin

sysadmin sp_diskdefault

sysadmin sp_dropapprole

sysadmin sp_dropdevice

sysadmin sp_droplinkedsrvlogin

sysadmin sp_droplogin

sysadmin sp_dropremotelogin

sysadmin sp_droprole

sysadmin sp_droprolemember

sysadmin sp_dropuser

sysadmin sp_fulltext_catalog

sysadmin sp_fulltext_column

sysadmin sp_fulltext_database

sysadmin sp_fulltext_service

sysadmin sp_fulltext_table

sysadmin sp_grantdbaccess

sysadmin sp_grantlogin

sysadmin sp_helplogins

sysadmin sp_password

sysadmin sp_recompile

sysadmin sp_refreshview

sysadmin sp_remoteoption

sysadmin sp_remoteoption (update)

sysadmin sp_rename

sysadmin sp_renamedb

sysadmin sp_revokedbaccess

sysadmin sp_revokelogin

sysadmin sp_tableoption

sysadmin sp_updatestats


sysadmin UPDATE permission on any object

sysadmin USE to a suspect database


CLR – Check if enabled, enable it.. etc – SQL Server

“The common language runtime (CLR) integration feature is off by default, and must be enabled in order to use objects that are implemented using CLR integration. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure in SQL Server Management Studio:”  ….  Microsoft

Essential commands:

SELECT * FROM sys.configurations
WHERE name = ‘clr enabled’

sp_configure ‘show advanced options’, 1;
–enable it
sp_configure ‘clr enabled’, 1;

–disable it
sp_configure ‘clr enabled’, 0;

SELECT * FROM sys.configurations
WHERE name = ‘clr enabled’

Diskspd – SQLIO Replacement

Diskspd Utility application. This is a new robust storage Testing Tool (superseding SQLIO).
“”A feature-rich and versatile storage testing tool, Diskspd  combines robust and granular IO workload definition with flexible runtime and output options, creating an ideal tool for synthetic storage subsystem testing and validation.””

Click here for the download link.

Sample output from a run on a basic PC hard drive.

d3 d2 d1