How to check the SQL sa Login is disabled

Often organisations have a SQL server security policy dictating the 'sa' login is disabled. There is some sound reasoning behind this policy. The primary reason is to decreases the surface area available to attack - and is a common principle of security. When this is a requirement - I'll add some code to disable 'sa' as part of the build and certification process. But , from time to time - the 'sa' login will be re enabled, so it's important to build an alerting or reporting mechanism identifying the exception. The sql statement to check if 'sa' is disabled. SELECT... Read more →

DELETE FROM OPENQUERY

Question: How can I DELETE using OPENQUERY? Normally for a SELECT from OPENQUERY , I'll do something like : SELECT col1 ,col2 FROM OPENQUERY (MY_LINKED_SERVER,'SELECT col1,col2 FROM MY_LINKED_TABLE') Can a similar method be used but for DELETE? Answer: To use DELETE , it's a similar process to the SELECT but using the DELETE syntax. Taking the SELECT example above - you'll do the following. Note:this example will delete ALL the rows in the linked server table . : DELETE FROM OPENQUERY (MY_LINKED_SERVER,'SELECT col1,col2 FROM MY_LINKED_TABLE') Read more on OPENQUERY,Linked Servers and AS400 SQL Server – Datetime date range , AS400... Read more →

Get Windows OS version using t-sql

Method 1 : Query sys.dm_os_windows SELECT windows_release, windows_service_pack_level, windows_sku, os_language_version FROM sys.dm_os_windows_info; Method 2 : Use xp_cmdshell - although this does mean enabling xp_cmdshell , which is in many organisations as security violation exec master..xp_cmdshell 'systeminfo' Method 3: The @@version returns system and build information for the current SQL Server. SELECT @@version Method 4: xp_msver returns details about the actual build number of the server and information about the server environment.This can be very useful if you want to integrated as part of stored procedure, batch or other type of t-sql code. xp_msver Sample output 1 ProductName NULL Microsoft SQL... Read more →

Powershell assign a drive and mount point

Question: What are the powershell commands to create to ASSIGN a drive letter and then create a MOUNT POINT? Answer: These powershell command examples will assign a drive letter and then create a mount point. You'll need to first know the Disk ID and also decide on the drive letter you need to assign If you want to create a mount point , then use the MOUNT POINT section If the intention is to automate the assigning of drive letters to new partitions , and also dynamically identify the diskid than you'll need to wrap some extra code around these... Read more →

Difference between High Availability and Disaster Recovery

Question: What is the difference between High Availability (HA) and Disaster Recovery (DR)? I often hear these terms being used interchangeably ,and would like to hear some specific definitions . Answer: High Availability and Disaster Recovery are often interchanged but there is a difference - and once we define the attributes of both High Availability and Disaster Recovery , you can then use them accordingly. HA is a system feature , with an objective to achieve an agreed operational uptime for a higher than normal period DR refers to procedures and policies to facilitate recovery of critical systems and infrastructure... Read more →

SQL Server Sysadmin Audit Report Powershell script

Question: I need a powershell script which reports on SQL Server logins with Sysadmin privileges. My aim is to automate the process - so if you could provide a script which provides the sql server name , login and that I can schedule. Answer: This powershell script will execute across a list of servers - as defined in the file associated with the $instancepath variable.Create a text file and place 1 or more sql server instances in the file e.g server1\instance1 server2\instance1 $isodate=Get-Date -format s $isodate=$isodate -replace(":","") $basepath=(Get-Location -PSProvider FileSystem).ProviderPath $instancepath=$basepath + "\config\instances_all.txt" $outputfile="\logs\sql_servers_sysadmin_report" + $isodate + ".html" $outputfilefull =... Read more →

Does SQL Server install Visual Studio components

Question: I was looking through Programs installed on a server with SQL Server installed and noticed Visual Studio components installed. As part of a SQL Server installation , which parts of the SQL Server install install the Visual Studio components? Answer: Since the 2016 version , SSMS is now based on the Visual Studio 2015 Shell. You could say SSMS is a Visual Studio Extension. The main reasoning , is the removal of dependency on installing .Net Framework 3.5 and some apparent performance improvements. Note:SSMS is now a standalone installation Useful reading on SSMS How to start SSMS as Administrator... Read more →