You are here


Discover How User Is Accessing SQL Instance (PowerShell and T-SQL)

When your security gets unruly, it can get time consuming to figure out how a user is accessing a SQL instance. Sometimes you may know that a user is getting access via one certain AD group but you aren't sure if any other groups are granting access. Furthermore, a user might be in a group that's nested in another group, through which they are gaining access to your SQL instance.

PowerShell With Uptime - Getting Days Only

There is a handy and free tool to check your system's uptime called "Uptime".  (Clever, no?)  It can be downloaded at

While it is very easy to use (just navigate to it in a command window and type "uptime"), it doesn't give much of an option for formatting the output to include only the days.  So, a simple workaround is to use some "replace" commands within PowerShell and only output the number of days.  Here's how:

PowerShell - Update All SQL Services To Restart On Failure

PowerShell script to quickly change all Recovery Options to "Restart the Service".

PowerShell - Create And Write Out To Log File

Creating and writing to output file in PowerShell.


Generate Red Gate MultiScript .dat File

This is a PowerShell script that puts out the XML required to update your Red Gate MultiScript servers.  Manually entering the SQL instances from within the application can be a daunting task if you have a large number to do.  This script will have you up and running in seconds.

You must first have a comma-separated list of your instances to load into the array.  Once you have that, set $instances equal to your list.  Notice that the .dat file will launch in Notepad to make it easy to paste in the results.  Then, save the file and you're done.

PowerShell - Check If Module Is Loaded

This is how to check if a particular module is loaded, and if it's not then load it. SQLPS is the module used in the example, but you can replace that with any module name.

PowerShell - Get All SQL Instances On Server

This script will return all SQL instances running on a server or list of servers.

Import-Module -Name SQLPS -DisableNameChecking

$servers = "SERVER_NAME", "SERVER_NAME_2", "ETC";

foreach($server in $servers)
    $path = "SQLServer:\SQL\" + $server;
    $instances = Get-ChildItem -Path $path;

PowerShell - Get "Logon As" Information From Remote (or local) Server

This was made possible by the Scripting Guy.  I have boiled it down to the answer.

If you want to use PowerShell to query for the log on account that normally is displayed in the services.msc service properties window:

Restore All Databases With Most Recent Backup

This is a bit of a scary task.  Someone tells you about 87 databases that all need to be restored from the most recent FULL backups. You can either spend the rest of your day pointing and clicking, or you can use some PowerShell power to crank out the script and get on with your day. Here is the PowerShell way.

I don't like to fire-off the actual backups from PowerShell, rather I just use it to script out the restore script.  So, this PowerShell script will output the T-SQL restore script:

(See caution below before running this!)

PowerShell - List All Directories With Users Who Can Access

Maybe this is a one-off type of thing, but if you need to see every folder within a directory and get the list of who can access that folder, it can be done using PowerShell. The output is tab-separated and can easily paste into an Excel sheet.


This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
14 + 1 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.

Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer