List Databases (and Properties) on SQL Server with PowerShell

Another post in the PowerShell Box of Tricks series. Here is another script which I use to save me time and effort during my daily workload enabling me to spend more time on more important (to me) things!

Todays question which I often get asked is What databases are on that server?

This is often a follow up to a question that requires the Find-Database script. It is often asked by support teams investigating issues. It can also be asked by developers checking the impact of other services on their DEV/UAT environments, by change managers investigating impact of changes, by service managers investigating the impact of downtime, when capacity planning for a new service and numerous other situations.

A simple quick and easy question made simpler with this function which can also be called when creating documentation

image

Simply call it with Show-DatabasesOnServer SERVERNAME and use the results as you need

image

This only shows you the name but if you need more information about your databases then have a look and see what you require.

Use Get-Member to see what is there. I ran the following code to count the number of Properties available for Databases (Using PowerShell V3 on SQL Server 2012 SP1 11.0.3350.0 )

image

154 Properties that you can examine and that is just for databases:-)

Picking out a few properties you could do something like this

image

If you want aliases for your column headings you will need to add a bit of code to the select.

For Example, maybe you want to Database Name as a heading and the Size in Gb (Its in Mb in the example above) You would need to create a hash table with a Label element and an Expression element. The Label is the column heading and the Expression can just be the data or a calculation on data.

So select Name becomes

The Column Heading is Database Name and the data is the Name property

and select Size becomes

The Column Heading is Size GB and the data is the Size property divided by 1024 to 3 decimal places

then your code would look like this

and the results

image

and here is the full code

 

Advertisements

Dropping SQL Users with PowerShell

As you may have noticed, I love PowerShell!

I have developed a series of functions over time which save me time and effort whilst still enabling me to provide a good service to my customers. I keep them all in a functions folder and call them whenever. I call it my PowerShell Box of Tricks

I am going to write a short post about each one over the next few weeks as I write my presentation on the same subject which I will be presenting to SQL User Groups.

Todays post is not about a question but about a routine task DBAs do. Dropping Logins

Whilst best practice says add users to active directory groups, add the group to roles and give the roles the correct permissions there are many situations where this is not done and DBAs are required to manually remove logins. This can be a time consuming task but one that is essential. There was a time at MyWork when this was achieved via a script that identified which servers had a users login and the task was to connect to each server in SSMS and remove the user from each database and then drop the server login. As you can imagine it was not done diligently. Prior to an audit I was tasked with ensuring that users that had left MyWork did not have logins to any databases. It was this that lead to the Checking for SQL Logins script and to this one

It starts exactly the same as the Checking for SQL Logins script by grabbing the list of SQL Servers from the text file and creating an array of user names including all the domains as I work in a multi-domain environment

image

Then iterate through each database ignoring those that may need special actions due to the application and call the drop method

image

Repeat the process for the servers and send or save the report as required. Simple and easy and has undoubtedly saved me many hours compared to the previous way of doing things 🙂

image

IMPORTANT NOTE

This script will not delete logins if they have granted permissions to other users. I always recommend running the Checking for SQL Logins script after running this script to ensure all logins have been dropped

This script can be found

 

Using PowerShell to find a database amongst hundreds

As you know, I love PowerShell!

I have developed a series of functions over time which save me time and effort whilst still enabling me to provide a good service to my customers. I also have a very simple GUI which I have set up for my colleagues to enable them to easily answer simple questions quickly and easily which I will blog about later. I call it my PowerShell Box of Tricks

I am going to write a short post about each one over the next few weeks as I write my presentation on the same subject which I will be presenting to SQL User Groups.

Todays question which I often get asked is Which server is that database on?

It isn’t always asked like that. Sometimes it is a developer asking where the database for their UAT/SAT/FAT/Dev environment is. Sometimes it is a manager who requires the information for some documentation or report. I wrote it because whilst I am good, I am not good enough to remember the server location for many hundreds of databases and also because I wanted to enable a new starter to be as self sufficient as possible

When I first wrote this I thought it would be a case of simply using $databaseName.Contains($DatabaseNameSearch) but this did not work for MiXed case searches or DatAbaSe names so I had to convert both the search and the database name to lower case first

image

I create an empty hash table and then populate it with the results

image

Set a results variable to the names from the hash table and count the number of records

image

and call it like this

image

Note that the search uses the contains method so no need for wildcards

Results come out like this

image

You can find the code here

 

Checking Drive Sizes with PowerShell

I have developed a series of PowerShell functions over time which save me time and effort whilst still enabling me to provide a good service to my customers.  I call it my PowerShell Box of Tricks and this is another post in the series.

Todays question which I often get asked is How much space is free on the drive?

A question often asked by developers during development and by DBAs when looking at provisioning new databases so I use this simple function to return the drive sizes using a WMI call with PowerShell

I first write the date out to the console with the Server name as I found that useful to show how much space had been freed when archiving data. Then a WMI query and a bit of maths and output to the console. The /1GB converts the drive size to something meaningful and you can see how the PercentFree is calculated from the two values using “{0:P2}”

image

Call it like this

image

and here are the results from my Azure VM. (See My previous posts on how to create your own Azure VMs with PowerShell)

image

You can find the script below

 

SQL Server Operators and Notifications with Powershell – Strange Enumerate issue fixed by @napalmgram

Alerting of issues across the SQL Server estate is important and recently I needed to audit the operators and the notifications that they were receiving.

I created a SQL Server Object

2013-09-04_125056

One of the important things to remember when investigating SMO is the Get-Member cmdlet. This will show all methods and properties of the object

gave me the JobServer Property

includes the Operator Property

2013-09-04_125717

has the EnumJobNotifications and EnumNotifications methods

So it was easy to loop through each server in the servers.txt file and enumerate the notifications for each Operator

2013-09-04_130052

and create a simple report

However this does not work as it does not perform the second enumerate. Try it yourself, switch round the EnumJobNotifications and EnumNotifications methods in that script and see what happens.

So I ended up with two functions

2013-09-04_174005

2013-09-04_173953

and I thought I could do this

2013-09-04_174056

But that doesnt work

So I tried this

2013-09-04_174112

and that doesnt work either

Now the reports are coming out showing the correct number of lines but not displaying them. I spent a period of time on my Azure boxes trying to work a way around this. I set the outputs to both enums to a variable and noted that they are different type of objects.

2013-09-05_113931

Job Notifications are System.Object and Alert Notifications are System.Array

I tried to enumerate through each member of the array and display them but got too tired to finish but I had contacted my friend Stuart Moore Twitter | Blog who had a look and resolved it by simply piping the Enumerates to Format-Table. Thank you Stuart.

So the final script is as follows

2013-09-05_114601

and the script is

 

SQL login object permissions via PowerShell

As you know, I love PowerShell!

I use it all the time in my daily job as a SQL DBA and at home whilst learning as well.

Not only do I use PowerShell for automating tasks such as Daily Backup Checks, Drive Space Checks, Service Running Checks, File Space Checks, Failed Agent Job Checks, SQL Error Log Checks, DBCC Checks and more but also for those questions which come up daily and interfere with concentrating on a complex or time consuming task.

I have developed a series of functions over time which save me time and effort whilst still enabling me to provide a good service to my customers. I keep them all in a functions folder and call them whenever I need them. I also have a very simple GUI which I have set up for my colleagues to enable them to easily answer simple questions quickly and easily which I will blog about later. I call it my PowerShell Box of Tricks

I am going to write a short post about each one over the next few weeks as I write my presentation on the same subject which I will be presenting to SQL User Groups.

Todays question which I often get asked is What permissions do users have on that server?

In the last post on Checking SQL Server User Role Membership with PowerShell we checked the permissions a user had across the estate, this one answers the question about all users on a server.

This is generally asked by DBAs of each other Smile, auditors and the owners of the service

The first part of the script is very similar to the last post on Checking SQL Server User Role Membership with PowerShell but we use the EnumMembers method to display the members of the roles.

image

The second part – the object permissions comes with thanks to David Levy via This Link

image

To call it simply load the function

image

and a report

image

You can get the code here

 

Checking SQL Server User Role Membership with PowerShell

As you know, I love PowerShell!

I use it all the time in my daily job as a SQL DBA and at home whilst learning as well.

Not only do I use PowerShell for automating tasks such as Daily Backup Checks, Drive Space Checks, Service Running Checks, File Space Checks, Failed Agent Job Checks, SQL Error Log Checks, DBCC Checks and more but also for those questions which come up daily and interfere with concentrating on a complex or time consuming task.

I have developed a series of functions over time which save me time and effort whilst still enabling me to provide a good service to my customers. I keep them all in a functions folder and call them whenever I need them. I also have a very simple GUI which I have set up for my colleagues to enable them to easily answer simple questions quickly and easily which I will blog about later. I call it my PowerShell Box of Tricks

I am going to write a short post about each one over the next few weeks as I write my presentation on the same subject which I will be presenting to SQL User Groups.

Todays question which I often get asked is Which roles is this user a member of?

Obviously it is not normally asked exactly like that but for a DBA that is the question. Following good practice we try to ensure that database permissions are granted by role membership and each role is creatd with the minimum amount of permissions required for successful execution of the task involved. So I only concentrate on Role Membership for this script. The script in the next post will deal with Object permissions.

This question can come from Support Desks when they are investigating a users issue, Developers when they are testing an application as well as audit activities.

I start by getting the list of servers from my text file and creating an array of logins for each domain as I work in a multi domain environment

Edit April 2015 – Whilst I still use this technique in my presentations, I have found a more useful method of doing this nowadays. I have a DBA database which holds information about all of the servers and databases that we have. This enables me to get a list of servers that I wish to check by using Invoke-SQLCMD2 and passing in a query to get the servers that I require. This enables me to filter by Live/Dev/Test Servers or by Operating System or by SQL Version or any other filter I wish to add

image

Then loop through the list of servers and check if the login exists on that server

image

To check which Role the user is a member of I use the EnumMembers method and assign that to an array and then check if the user exists in the array

image

I do the same for the database roles

image

I then call it as follows

image

And get a handy report

image

Here is the full code

 

Checking for SQL Server logins with PowerShell

As some of you may know, I love PowerShell!

I use it all the time in my daily job as a SQL DBA and at home whilst learning as well.

Not only do I use PowerShell for automating tasks such as Daily Backup Checks, Drive Space Checks, Service Running Checks, File Space Checks, Failed Agent Job Checks, SQL Error Log Checks, DBCC Checks and more but also for those questions which come up daily and interfere with concentrating on a complex or time consuming task.

I have developed a series of functions over time which save me time and effort whilst still enabling me to provide a good service to my customers. I keep them all in a functions folder and call them whenever I need them. I also have a very simple GUI which I have set up for my colleagues to enable them to easily answer simple questions quickly and easily which I will blog about later. I call it my PowerShell Box of Tricks

I am going to write a short post about each one over the next few weeks as I write my presentation on the same subject which I will be presenting to SQL User Groups.

Todays question which I often get asked is Which database does this account have access to?

This question can come from Support Desks when they are investigating a users issue, Developers when they are testing an application as well as audit activities. It is usually followed by what permissions do they have which is covered by my next blog post.

I start by getting the list of servers from my text file and creating an array of logins for each domain as I work in a multi domain environment

image

Then loop through each server and if the login exists write it out to the window.

image

I then repeat this but loop through each database as well

image

A little bit of formatting is added and then a quick easy report that can easily be copied to an email as required.

To call it simply load the function

image

and get the results

image

The code is below